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!
July 3, 2015 at 11:50 AM
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.
July 3, 2015 at 1:16 PM
Hi Leslie,
Unfortunately, as far as I know, you can’t recover truncated data. Haven’t you a database backup?
March 20, 2011 at 2:10 AM
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
March 22, 2011 at 4:52 AM
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
September 9, 2010 at 11:20 AM
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!!!!!
September 9, 2010 at 7:57 AM
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
August 30, 2010 at 2:58 AM
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.
August 30, 2010 at 10:36 AM
Thank you very much Victor. I don’t know why these wasn’t in the query…
Post already altered!
August 24, 2010 at 7:13 AM
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 !
August 30, 2010 at 10:37 AM
Post updated! Thank you Donafriou!
August 21, 2010 at 12:49 AM
Nice one, Cezar
August 22, 2010 at 6:42 AM
Thank you Uli!