ODI snippets: Purge Log and shrink space
Get our e-books Discover the Oracle Data Integrator 11g Repository Data Model and Oracle Data Integrator Snippets and Recipes
We all know that we should purge the ODI log on a regular basis. if we log everything or if we run near real time batches. You can either purge the log manually from the Operator module or you can use the ODIPurgeLog tool.
As per Metalink 424663.1 the following tables contain information concerning Session runtime executions
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SNP_SESS_TXT_LOG SNP_SESS_TASK_LOG SNP_STEP_LOG SNP_TASK_TXT SNP_SESS_TASK SNP_SESS_STEP SNP_SEQ_SESS SNP_VAR_SESS SNP_SESSION SNP_EXP_TXT (starting with ODI 10.1.3 versions): SNP_SESS_TASK_LOG.I_TXT_TASK_MESS = SNP_EXP_TXT.I_TXT SNP_STEP_LOG.I_TXT_STEP_MESS = SNP_EXP_TXT.I_TXT SNP_VAR_SESS.I_TXT_VAR = SNP_EXP_TXT.I_TXT SNP_VAR_SESS.I_TXT_DEF_T = SNP_EXP_TXT.I_TXT SNP_SESSION.I_TXT_SESS_MESS = SNP_EXP_TXT.I_TXT SNP_SESSION.I_TXT_SESS_PARAMS = SNP_EXP_TXT.I_TXT |
When you perform a purge log these are the tables that are cleaned up. The tables are cleaned up via DELETE DML. As a result space is not released and the is not lowered.
In order to release the unused space you can shrink the tables from Oracle 10 onwards, e.g.:
|
1 2 3 |
ALTER TABLE snp_exp_txt ENABLE ROW MOVEMENT; ALTER TABLE snp_exp_txt SHRINK SPACE; ALTER TABLE snp_exp_txt DISABLE ROW MOVEMENT; |
[big_data_promotion]
Uli Bethke
Uli has been rocking the data world since 2001. As the Co-founder of Sonra, the data liberation company, he’s on a mission to set data free. Uli doesn’t just talk the talk—he writes the books, leads the communities, and takes the stage as a conference speaker.
Any questions or comments for Uli? Connect with him on LinkedIn.
Follow Uli Bethke: 
