Posted by : Arjun Lagisetty Tuesday, June 13, 2017

Problem: 

What happens when you realize that you a need to clear years worth of data from the ODI work repository?

Whats wrong with just purging using purge option using Operator?
    You are most likely to run out of undo table space with error. 

ORA-30036: unable to extend segment by 8 in undo tablespace '<Undo Tablespace name>'

Solution:

    Delete logs in small batches one day of logs at a time. Using OdiTools and a procedure.

1. Create a procedure with the following "Command on Target" code
OdiPurgeLog "-PURGE_TYPE=ALL" "-FROMDATE=#FROM_DT" "-TODATE=#TO_DT" "-PURGE_REPORTS=YES" "-XML_CHARSET=ISO-8859-1" "-JAVA_CHARSET=ISO8859_1"



  2. Add the following "Command on Source" code

SELECT TRUNC (SYSDATE - ROWNUM) TO_DT, TRUNC (SYSDATE - ROWNUM - 1) FROM_DT
      FROM DUAL
CONNECT BY ROWNUM < 366

Leave a Reply

Subscribe to Posts | Subscribe to Comments

Popular Post

Labels

Blog Archive

Copyright © ODI Pundits - Oracle Data Integrator - Maintained by Arjun Lagisetty