Hi People,

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!