There is a “bug” in Oracle Data Integrator that doesn’t clean the SNP_EXP_TXT when the “parent” records are deleted.
That happens because, in the repository data model, there is no FK between the SNP_EXP_TXT and its several parents table exactly because they are several.
This situation can bring a huge increase of records and, the most of then, absolutely useless. I already see environments with more than 50 millions of records and with less than 100 k useful…
A consequence of this behavior could be a very slow answer when try to acess objects from Designer and/or Operator. That is very commom.
To solve, all you need is just execute the following DML (NOT TESTED ON 11G, ONLY FOR 10 version):WARNNIG: THIS IS A DML AGAINST ODI WORK REPOSITORY TABLES. ONLY EXECUTE IT WITH ALL TABLES IN UNION SUB-QUERY. IF NOT, YOU CAN DELETE VALID RECORDS AND SEVERLY DAMAGE YOUR ODI INSTALATION UP TO UNRECOVERY STATE.
delete snp_exp_txt where not exists (select 0 from ( select distinct I_TXT_TASK_MESS I_TXT from SNP_SESS_TASK_LOG union all select distinct I_TXT_SCEN I_TXT from SNP_SCEN union all select distinct I_TXT_SESS_MESS I_TXT from SNP_SCEN_REPORT union all select distinct I_TXT_DESCRIPTION I_TXT from SNP_SCEN_FOLDER union all select distinct I_TXT_SESS_MESS I_TXT from SNP_SESSION union all select distinct I_TXT_SESS_PARAMS I_TXT from SNP_SESSION union all select distinct I_TXT_STEP_MESS I_TXT from SNP_STEP_REPORT union all select distinct I_TXT_STEP_MESS I_TXT from SNP_STEP_LOG union all select distinct I_TXT_VAR I_TXT from SNP_VAR_SESS union all select distinct I_TXT_DEF_T I_TXT from SNP_VAR_SESS union all select distinct I_TXT_VAR I_TXT from SNP_VAR_SCEN union all select distinct I_TXT_DEF_T I_TXT from SNP_VAR_SCEN union all select DISTINCT I_TXT as I_TXT FROM SNP_TXT union all select DISTINCT I_TXT_VAR_IN as I_TXT FROM SNP_VAR union all select DISTINCT I_TXT_VAR_T as I_TXT FROM SNP_VAR_DATA) as UNION_TABLES where UNION_TABLES.I_TXT = snp_exp_txt.I_TXT )
This is an Oracle DB query, adapt it for you Work Repository technology.
You can, either, create a Topology to Work Repository and use an ODI Procedure to schedule this DML once a month, for instance!
As always, comments are welcome!!
Talk to you soon, friends!