ODI 12c - Procedure Purge Log 2

Di seguito la Purge Manuale dei LOG di ODI, da utilizzare solo dopo aver ricevuto l'OK dal supporto Oracle. Tale procedura se utilizzata senza consenso del supporto potrebbe portare alla cessazione della garanzia del prodotto in quanto prevede la cancellazione di tabelle del repository ODI, la cui manomissione comporta la cessazione della garanzia.

• STEP 1

Estrazione di tutte le tabelle del repository su cui effettuare una count prima della Purge dei dati, connettersi allo schema proprietario del repository ed effettuare la seguente query per estrarsi uno script di count su tutte le tabelle.:

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

COUNT ALL TABLE REPOSITORY 

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

select  

'select '''||table_name||''' as TABLE_NAME, count(*) as REC_NUM from '||table_name||' UNION '

from user_tables

order by table_name;

• STEP 2

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

----  VERIFICARE IL CONTENUTO DELLA TABELLA SNP_VAR_SESS -----------

----  SE DIFFERENTE DA 0 OCCORRE EFFETTUARE UN DELETE SULLA --------

----  TABELLA SNP_EXP_TXT_HEADER -----------------------------------

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

---------------- CLEANING SNP_EXP_TXT_HEADER -----------------------

    

    ALTER TABLE SNP_EXP_TXT_HEADER NOLOGGING;

   

    DELETE FROM SNP_EXP_TXT_HEADER 

        WHERE I_TXT IN ( SELECT I_TXT_VAR FROM SNP_VAR_SESS );

    

    ALTER TABLE SNP_EXP_TXT_HEADER LOGGING;

    

----------  DA FARE PRIMA DELLA TRUNCATE TABLE SNP_VAR_SESS ---------

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

• STEP 3

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

--- BACKUP TABELLE SNP_SESSION --- SNP_SESSION_REPORT ---- SNP_PLAN_AGENT 

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

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

- Purge ODI Logs (Procedura manuale con truncate table)

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

-- A,C

alter table SNP_PARAM_SESS disable constraint FK_PARAM_SESS;

alter table SNP_SESS_STEP disable constraint FK_SESS_STEP;

alter table SNP_SESS_TASK disable constraint FK_SESS_TASK3;

alter table SNP_VAR_SESS disable constraint FK_VAR_SESS;

alter table SNP_SCHEDULE_EXEC disable constraint FK_CURRENT_SESSION;

alter table SNP_SCHEDULE_EXEC disable constraint FK_SCHEDULE_ID;

alter table SNP_SCHEDULE_EXEC disable constraint FK_CURRENT_LP_RUN;

alter table SNP_SESS_TASK_LS disable constraint FK_SESS_TASK_LS;

alter table SNP_SESS_STEP_LV disable constraint FK_SESS_STEP_LV;

alter table SNP_SESS_TASK_LOG disable constraint FK_SESS_TASK_LOG;

alter table SNP_STEP_LOG disable constraint FK_STEP_LOG;

alter table SNP_SESSION disable constraint FK_SNP_SESSION_SB;

truncate table SNP_PARAM_SESS;

truncate table SNP_SESS_STEP;

truncate table SNP_SESS_TASK;

truncate table SNP_VAR_SESS;

truncate table SNP_SCHEDULE_EXEC;

truncate table SNP_SESS_TASK_LS;

truncate table SNP_SESS_STEP_LV;

truncate table SNP_SESS_TASK_LOG;

truncate table SNP_STEP_LOG;

truncate table SNP_SESSION;

alter table SNP_PARAM_SESS enable constraint FK_PARAM_SESS;

alter table SNP_SESS_STEP enable constraint FK_SESS_STEP;

alter table SNP_SESS_TASK enable constraint FK_SESS_TASK3;

alter table SNP_VAR_SESS enable constraint FK_VAR_SESS;

alter table SNP_SCHEDULE_EXEC enable constraint FK_CURRENT_SESSION;

alter table SNP_SCHEDULE_EXEC enable constraint FK_SCHEDULE_ID;

alter table SNP_SCHEDULE_EXEC enable constraint FK_CURRENT_LP_RUN;

alter table SNP_SESS_STEP_LV enable constraint FK_SESS_STEP_LV;

alter table SNP_SESS_TASK_LOG enable constraint FK_SESS_TASK_LOG;

alter table SNP_SESS_TASK_LS enable constraint FK_SESS_TASK_LS;

alter table SNP_STEP_LOG enable constraint FK_STEP_LOG;

alter table SNP_SESSION enable constraint FK_SNP_SESSION_SB;

• STEP 4 

alter table SNP_STEP_REPORT disable constraint FK_STEP_REPORT;

alter table SNP_SCEN_REPORT disable constraint FK_SCEN_REPORT;

truncate table SNP_STEP_REPORT;

truncate table SNP_SCEN_REPORT;

alter table SNP_STEP_REPORT enable constraint FK_STEP_REPORT;

alter table SNP_SCEN_REPORT enable constraint FK_SCEN_REPORT;

• STEP5

alter table SNP_LPI_EC_VAR_LOG disable constraint FK_LPI_EVLOG_E

alter table SNP_LPI_EC_VAR_LOG disable constraint FK_LPI_EVLOG_S

alter table SNP_LPI_EXC_LOG disable constraint FK_LPI_ELOG_SLOG;

alter table SNP_LPI_EXC_LOG disable constraint FK_LPI_ELOG_STEP;

alter table SNP_LPI_VAR_LOG disable constraint FK_LPI_VLOG_SLOG;

alter table SNP_LPI_VAR_LOG disable constraint FK_LPI_VLOG_SVAR;

alter table SNP_LPI_STEP_LOG disable constraint FK_LPI_SLOG_RUN;

alter table SNP_LPI_STEP_LOG disable constraint FK_LPI_SLOG_STEP

truncate table SNP_LPI_EC_VAR_LOG;

truncate table SNP_LPI_EXC_LOG;

truncate table SNP_LPI_VAR_LOG;

truncate table SNP_LPI_STEP_LOG;

alter table SNP_LPI_EC_VAR_LOG enable constraint FK_LPI_EVLOG_EL

alter table SNP_LPI_EC_VAR_LOG enable constraint FK_LPI_EVLOG_SV

alter table SNP_LPI_EXC_LOG enable constraint FK_LPI_ELOG_SLOG;

alter table SNP_LPI_EXC_LOG enable constraint FK_LPI_ELOG_STEP;

alter table SNP_LPI_VAR_LOG enable constraint FK_LPI_VLOG_SLOG;

alter table SNP_LPI_VAR_LOG enable constraint FK_LPI_VLOG_SVAR;

alter table SNP_LPI_STEP_LOG enable constraint FK_LPI_SLOG_RUN;

alter table SNP_LPI_STEP_LOG enable constraint FK_LPI_SLOG_STEP;

• STEP 6 

alter table SNP_PARAM_SESS disable constraint FK_PARAM_SESS;

alter table SNP_SESS_STEP disable constraint FK_SESS_STEP;

alter table SNP_SESS_TASK disable constraint FK_SESS_TASK3;

alter table SNP_VAR_SESS disable constraint FK_VAR_SESS;

alter table SNP_SCHEDULE_EXEC disable constraint FK_CURRENT_SESSION;

alter table SNP_SCHEDULE_EXEC disable constraint FK_SCHEDULE_ID;

alter table SNP_SCHEDULE_EXEC disable constraint FK_CURRENT_LP_RUN;

alter table SNP_SESS_TASK_LS disable constraint FK_SESS_TASK_LS;

alter table SNP_SESS_STEP_LV disable constraint FK_SESS_STEP_LV;

alter table SNP_SESS_TASK_LOG disable constraint FK_SESS_TASK_LOG;

alter table SNP_STEP_LOG disable constraint FK_STEP_LOG;

alter table SNP_SESSION disable constraint FK_SNP_SESSION_SB;

alter table SNP_STEP_REPORT disable constraint FK_STEP_REPORT;

alter table SNP_SCEN_REPORT disable constraint FK_SCEN_REPORT;

alter table SNP_LPI_EC_VAR_LOG disable constraint FK_LPI_EVLOG_ELOG;

alter table SNP_LPI_EC_VAR_LOG disable constraint FK_LPI_EVLOG_SVAR;

alter table SNP_LPI_EXC_LOG disable constraint FK_LPI_ELOG_SLOG;

alter table SNP_LPI_EXC_LOG disable constraint FK_LPI_ELOG_STEP;

alter table SNP_LPI_VAR_LOG disable constraint FK_LPI_VLOG_SLOG;

alter table SNP_LPI_VAR_LOG disable constraint FK_LPI_VLOG_SVAR;

alter table SNP_LPI_STEP_LOG disable constraint FK_LPI_SLOG_RUN;

alter table SNP_LPI_STEP_LOG disable constraint FK_LPI_SLOG_STEP;

Tabelle da Troncare

truncate table SNP_PARAM_SESS;

truncate table SNP_SESS_STEP;

truncate table SNP_SESS_TASK;

truncate table SNP_VAR_SESS;

truncate table SNP_SCHEDULE_EXEC;

truncate table SNP_SESS_TASK_LS;

truncate table SNP_SESS_STEP_LV;

truncate table SNP_SES S_TASK_LOG;

truncate table SNP_STEP_LOG;

truncate table SNP_SESSION;

truncate table SNP_STEP_REPORT;

truncate table SNP_SCEN_REPORT;

truncate table SNP_LPI_EC_VAR_LOG;

truncate table SNP_LPI_EXC_LOG;

truncate table SNP_LPI_VAR_LOG;

truncate table SNP_LPI_STEP_LOG;

alter table SNP_PARAM_SESS enable constraint FK_PARAM_SESS;

alter table SNP_SESS_STEP enable constraint FK_SESS_STEP;

alter table SNP_SESS_TASK enable constraint FK_SESS_TASK3;

alter table SNP_VAR_SESS enable constraint FK_VAR_SESS;

alter table SNP_SCHEDULE_EXEC enable constraint FK_CURRENT_SESSION;

alter table SNP_SCHEDULE_EXEC enable constraint FK_SCHEDULE_ID;

alter table SNP_SCHEDULE_EXEC enable constraint FK_CURRENT_LP_RUN;

alter table SNP_SESS_STEP_LV enable constraint FK_SESS_STEP_LV;

alter table SNP_SESS_TASK_LOG enable constraint FK_SESS_TASK_LOG;

alter table SNP_SESS_TASK_LS enable constraint FK_SESS_TASK_LS;

alter table SNP_STEP_LOG enable constraint FK_STEP_LOG;

alter table SNP_SESSION enable constraint FK_SNP_SESSION_SB;

alter table SNP_STEP_REPORT enable constraint FK_STEP_REPORT;

alter table SNP_SCEN_REPORT enable constraint FK_SCEN_REPORT;

alter table SNP_LPI_EC_VAR_LOG enable constraint FK_LPI_EVLOG_ELOG;

alter table SNP_LPI_EC_VAR_LOG enable constraint FK_LPI_EVLOG_SVAR;

alter table SNP_LPI_EXC_LOG enable constraint FK_LPI_ELOG_SLOG;

alter table SNP_LPI_EXC_LOG enable constraint FK_LPI_ELOG_STEP;

alter table SNP_LPI_VAR_LOG enable constraint FK_LPI_VLOG_SLOG;

alter table SNP_LPI_VAR_LOG enable constraint FK_LPI_VLOG_SVAR;

alter table SNP_LPI_STEP_LOG enable constraint FK_LPI_SLOG_RUN;

alter table SNP_LPI_STEP_LOG enable constraint FK_LPI_SLOG_STEP;

Al termine eseguire la count ottenuta allo STEP 1 per la verifica di quanto cancellato.

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.

ODI 12c - repository tables and their description

 In the post the description of tables present in the repository ODI 12c.

• DESIGN - MASTER - RUNTIME