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.