[ODI 11G Repository] Purge Log by Manual through Procedure

more about
How To Manually Delete ODI Sessions And Scenario-Related Reports (Doc ID 424740.1)		
https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=68491857791385&id=424740.1&_afrWindowMode=0&_adf.ctrl-state=vmkjp9nwu_95

CREATE OR REPLACE PROCEDURE REPWRK_DMP.ODI_DELETE_LOG_BYDATE

/***

Author : WideTNS Co.

Usage  : exec ODI_DELETE_LOG_BYDATE('20100101000000');

Producted date : 2015.03.30

***/

(

    P_DATE in VARCHAR2/* Format : 'YYYYMMDDHH24MISS' */

)

IS

    V_DATE DATE := TO_DATE(P_DATE,'YYYYMMDDHH24MISS');

BEGIN

--Prepare the temporary objects.

--create table REPWRK_DMP.XXX_SESSION_TO_DELETE

--( SESS_NO NUMBER(19) NOT NULL, SESS_NAME VARCHAR2(400), SCEN_VERSION VARCHAR2(35), I_TXT_SESS_MESS NUMBER(19), I_TXT_SESS_PARAMS NUMBER(19), primary key (SESS_NO) );

--create index REPWRK_DMP.IDX_NAME   on REPWRK_DMP.XXX_SESSION_TO_DELETE (SESS_NAME);

--create index REPWRK_DMP.IDX_MESS   on REPWRK_DMP.XXX_SESSION_TO_DELETE (I_TXT_SESS_MESS);

--create index REPWRK_DMP.IDX_PARAMS on REPWRK_DMP.XXX_SESSION_TO_DELETE (I_TXT_SESS_PARAMS);

--dbms_stats.gather_table_stats (ownname => 'REPWRK_DMP', tabname => 'XXX_SESSION_TO_DELETE', estimate_percent => dbms_stats.AUTO_SAMPLE_SIZE);

--ALTER TABLE REPWRK_DMP.XXX_SESSION_TO_DELETE ENABLE ROW MOVEMENT;

--ALTER TABLE REPWRK_DMP.XXX_SESSION_TO_DELETE SHRINK SPACE CASCADE;

    EXECUTE IMMEDIATE 'TRUNCATE TABLE REPWRK_DMP.XXX_SESSION_TO_DELETE';

    --Set the delete criteria.

    insert into REPWRK_DMP.XXX_SESSION_TO_DELETE (SESS_NO, SESS_NAME, I_TXT_SESS_MESS, I_TXT_SESS_PARAMS)

    select SESS_NO, SESS_NAME, I_TXT_SESS_MESS, I_TXT_SESS_PARAMS from REPWRK_DMP.SNP_SESSION where SESS_BEG <= V_DATE;

    COMMIT;

    --SQL instructions to delete ODI 11g specific (11.1.1.3.0 and later) Session Header parameters and messages.

    delete from REPWRK_DMP.SNP_EXP_TXT_HEADER where I_TXT in (select I_TXT_TASK_MESS   from REPWRK_DMP.SNP_SESS_TASK_LOG where SESS_NO in (select SESS_NO from REPWRK_DMP.XXX_SESSION_TO_DELETE));

    delete from REPWRK_DMP.SNP_EXP_TXT_HEADER where I_TXT in (select I_TXT_STEP_MESS   from REPWRK_DMP.SNP_STEP_LOG      where SESS_NO in (select SESS_NO from REPWRK_DMP.XXX_SESSION_TO_DELETE));

    delete from REPWRK_DMP.SNP_EXP_TXT_HEADER where I_TXT in (select I_TXT_VAR         from REPWRK_DMP.SNP_VAR_SESS      where SESS_NO in (select SESS_NO from REPWRK_DMP.XXX_SESSION_TO_DELETE));

    delete from REPWRK_DMP.SNP_EXP_TXT_HEADER where I_TXT in (select I_TXT_DEF_T       from REPWRK_DMP.SNP_VAR_SESS      where SESS_NO in (select SESS_NO from REPWRK_DMP.XXX_SESSION_TO_DELETE));

    delete from REPWRK_DMP.SNP_EXP_TXT_HEADER where I_TXT in (select I_TXT_SESS_PARAMS from REPWRK_DMP.XXX_SESSION_TO_DELETE);

    delete from REPWRK_DMP.SNP_EXP_TXT_HEADER where I_TXT in (select I_TXT_SESS_MESS   from REPWRK_DMP.XXX_SESSION_TO_DELETE);

    --SQL instructions to delete ODI Session execution reports (applies to all ODI and Sunopsis versions).

    delete from REPWRK_DMP.SNP_SESS_TXT_LOG  where SESS_NO in (select SESS_NO from REPWRK_DMP.XXX_SESSION_TO_DELETE);

    delete from REPWRK_DMP.SNP_SESS_TASK_LS  where SESS_NO in (select SESS_NO from REPWRK_DMP.XXX_SESSION_TO_DELETE);

    delete from REPWRK_DMP.SNP_SESS_TASK_LOG where SESS_NO in (select SESS_NO from REPWRK_DMP.XXX_SESSION_TO_DELETE);

    delete from REPWRK_DMP.SNP_SESS_STEP_LV  where SESS_NO in (select SESS_NO from REPWRK_DMP.XXX_SESSION_TO_DELETE);

    delete from REPWRK_DMP.SNP_STEP_LOG      where SESS_NO in (select SESS_NO from REPWRK_DMP.XXX_SESSION_TO_DELETE);

    delete from REPWRK_DMP.SNP_TASK_TXT      where SESS_NO in (select SESS_NO from REPWRK_DMP.XXX_SESSION_TO_DELETE);

    delete from REPWRK_DMP.SNP_SESS_TASK     where SESS_NO in (select SESS_NO from REPWRK_DMP.XXX_SESSION_TO_DELETE);

    delete from REPWRK_DMP.SNP_SESS_STEP     where SESS_NO in (select SESS_NO from REPWRK_DMP.XXX_SESSION_TO_DELETE);

    delete from REPWRK_DMP.SNP_SEQ_SESS      where SESS_NO in (select SESS_NO from REPWRK_DMP.XXX_SESSION_TO_DELETE);

    delete from REPWRK_DMP.SNP_VAR_SESS      where SESS_NO in (select SESS_NO from REPWRK_DMP.XXX_SESSION_TO_DELETE);

    delete from REPWRK_DMP.SNP_SESSION       where SESS_NO in (select SESS_NO from REPWRK_DMP.XXX_SESSION_TO_DELETE);

    --SQL instructions to delete ODI 11g specific (11.1.1.3.0 and later) Scenario Header associated messages.

    delete from REPWRK_DMP.SNP_EXP_TXT_HEADER where I_TXT in (select I_TXT_STEP_MESS from REPWRK_DMP.SNP_STEP_REPORT where STEP_BEG <= V_DATE);

    delete from REPWRK_DMP.SNP_EXP_TXT_HEADER where I_TXT in (select I_TXT_SESS_MESS from REPWRK_DMP.SNP_SCEN_REPORT where SESS_BEG <= V_DATE);

    --SQL instructions to delete ODI Scenario associated reports (applies to all ODI and Sunopsis versions).

    delete from REPWRK_DMP.SNP_STEP_REPORT where STEP_BEG <= V_DATE;

    delete from REPWRK_DMP.SNP_SCEN_REPORT where SESS_BEG <= V_DATE;

    COMMIT;

END;