ODI 12C - Procedure Purge Log 1

 Di seguito una procedura pl-sql per effettuare la purge dei log ODI. La procedura permette di effettuare la delete dei record all'interno di alcune tabelle del repository di ODI.

CREATE OR REPLACE PROCEDURE purge_odi_sessions AS
    a number;
    b number;
    type tableArray IS VARRAY(15) OF VARCHAR2(30);
    tableNames tableArray;
    tableCount integer;
    query_string varchar(2000);
    filter_cond varchar(2000);
    concatString varchar(3);
    TYPE cur_typ IS REF CURSOR;
    c cur_typ;
    tbl_name varchar(100);
    con_name varchar(100);
    
BEGIN    
   tableNames := tableArray('<Inserire il nome delle tabelle tra apici singoli>');
   tableCount := tableNames.count;
   filter_cond:=' ';
   concatString:='''';
    FOR i in 1 .. tableCount LOOP
      filter_cond := filter_cond || concatString ||  tableNames(i);
      concatString:=''',''';
    END LOOP;
   filter_cond:=filter_cond||'''';
    dbms_output.put_line('===========================================' );
dbms_output.put_line('=== DISABLING FORIEGN KEYS ================' );
    
query_string := 'select  UC.TABLE_NAME,UC.CONSTRAINT_NAME
             FROM USER_CONSTRAINTS  UC,
         USER_CONS_COLUMNS UCC
   WHERE UC.R_CONSTRAINT_NAME = UCC.CONSTRAINT_NAME
     AND uc.constraint_type = ''R'' and UCC.TABLE_NAME in ('||filter_cond||') 
   ORDER BY UC.TABLE_NAME,
            UC.R_CONSTRAINT_NAME,
            UCC.TABLE_NAME,
            UCC.COLUMN_NAME';
    OPEN c FOR query_string;
    LOOP
        FETCH c INTO tbl_name, con_name;
         EXIT WHEN c%NOTFOUND;
         DBMS_OUTPUT.put_line ('EXECUTING : ' || 'ALTER TABLE '||tbl_name||' DISABLE CONSTRAINT '||con_name||';');
         execute immediate 'ALTER TABLE '||tbl_name||' DISABLE CONSTRAINT '||con_name;
    END LOOP;
    CLOSE c;
         
    dbms_output.put_line('===========================================' );
dbms_output.put_line('==== CLEANING SNP_EXP_TXT_HEADER =========' );

execute immediate 'ALTER TABLE SNP_EXP_TXT_HEADER NOLOGGING';
dbms_output.put_line('EXECUTING : DELETE FROM SNP_EXP_TXT_HEADER WHERE I_TXT IN ( SELECT I_TXT_VAR FROM SNP_VAR_SESS ); WITH OUT LOGGING' );
    
DELETE FROM SNP_EXP_TXT_HEADER WHERE I_TXT IN ( SELECT I_TXT_VAR FROM SNP_VAR_SESS );
    
execute immediate 'ALTER TABLE SNP_EXP_TXT_HEADER LOGGING';
    
    dbms_output.put_line('===========================================' );
dbms_output.put_line('======== TRUNCATING THE DATA =============' );
    
    FOR i in 1 .. tableCount LOOP
      dbms_output.put_line('EXECUTING : ' || 'TRUNCATE TABLE '|| tableNames(i) );
      execute immediate 'TRUNCATE TABLE '|| tableNames(i);
    
    END LOOP;
 
    dbms_output.put_line('===========================================' );
dbms_output.put_line('========= ENABLING FORIEGN KEYS ===========' );
     
    OPEN c FOR query_string;
    LOOP
        FETCH c INTO tbl_name, con_name;
         EXIT WHEN c%NOTFOUND;
         DBMS_OUTPUT.put_line ('EXECUTING : ' || 'ALTER TABLE '||tbl_name||' ENABLE CONSTRAINT '||con_name||';');
         execute immediate 'ALTER TABLE '||tbl_name||' ENABLE CONSTRAINT '||con_name;
     END LOOP;
    CLOSE c;
   
   DBMS_OUTPUT.PUT_LINE('Process Completed ');
 END purge_odi_sessions;
/
-- Execute the stored proc using below commads
-- set serveroutput on;
-- exec purge_odi_sessions;

---------------------------------------------------------------------------------------------------------------------------

Funziona ma non fa altro che seguire quanto viene fatto nell'oditools utilizzato per fare la purge, quindi l'inconveniente maggiore è il tempo impiegato per cancellare i dati dalle tabelle.