ODIExperts.com

The blog for Oracle Data Integrator ( ODI )

SNP_EXP_TXT – A “huge” table problem

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!

12 Comments

  1. hi cezar, is there a way to recover data from a truncated table in oracle odi 10g? i accidentally truncated the snp_exp_txt 🙁

    i need to recover it so that i can perform renumbering to resolve “unique constraint PK_EXP_TXT” behavior. i need to do this because my ODI scenarios are already hanging.

    your help and advice will be very much appreciated, thank you.

    • Hi Leslie,

      Unfortunately, as far as I know, you can’t recover truncated data. Haven’t you a database backup?

  2. hi,

    i have more than 30 millions data in this table. so the delete query takes a lot of time.

    can you tell me if these actions can resolve the problem :

    – create a new table SNP_EXP_TXT2
    – stop ODI,
    – copy only the good data in SNP_EXP_TXT2 from SNP_EXP_TXT
    – truncate SNP_EXP_TXT
    – rename SNP_EXP_TXT2 to SNP_EXP_TXT
    – analyse table.

    thank you for your answer

    best regards

    neuil

    • Hello Neuil,

      Yes, I believe it will work but instead truncate the original table, rename it for a while just in case… truncate after ODI be tested if everything works fine

      Just be careful to copy all constraints from SNP_EXP_TXT.

      Best Regards,

      Cezar

  3. No Cecile!!!

    The Blog wants to be cooperative… no problem if we do something wrong ang receive so important contribution to correct!!!

    Table added!

    Thank you Cecile!!!!!

  4. Hi Cezar … great post again but … SNP_VAR_DATA is still missing in the posted query.
    As this table may contains at a time a lot of records it may increase significantly SNP_EXP_TXT … sorry to be negative in one of my first post 😉
    Regards
    Cecile

  5. Hi all.

    I have found another 2 tables which possible linked to I_TXT identifier from snp_exp_txt. So my proposal is to add this:

    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

    to subquery.

  6. I added a table into the query : SNP_VAR_DATA and a restriction :

    delete from 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_T I_TXT from SNP_VAR_DATA
    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_TABLES
    where UNION_TABLES.I_TXT = snp_exp_txt.I_TXT
    )
    and snp_exp_txt.first_date < to_date('15/02/2009','DD/MM/YYYY');

    It works fine. Thanks you, Cezar !

Leave a Reply

Required fields are marked *.