ODI - Limpeza Sessões de Execução

Em ambientes onde o espaço físico do banco é um tanto limitado, é importante realizar limpezas periódicas das sessões de execução do Operator. Pode-se realizar isso manualmente, selecionando cada sessão (ou um conjunto) e excluír.
Pode-se também usar o botão de limpeza () e informar os parâmetros conforme tela abaixo:



Porém o processo acima pode ocasionar um erro caso a área de UNDO do banco seja limitada e/ou não esteja com a opção AUTO EXTEND. Esse processo de limpeza realiza a delação de registros de várias tabelas do repositório de trabalho do ODI, porém o COMMIT é realizado somente no final de todo processo, o que faz que a área de UNDO fique um pouco "inchada".
Uma solução é a limpeza via script das sessões do Operator diretamente na base de trabalho fazendo commit a cada tabela excluída.
Abaixo segue a lista de comandos para execução dessa tarefa:


--crio uma tabela para armazenar os IDs das sessões que quero excluir:
create table 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 IDX_XXX_SESSION_TO_DELETE_NAME
on XXX_SESSION_TO_DELETE (SESS_NAME);

create index IDX_XXX_SESSION_TO_DELETE_MESS
on XXX_SESSION_TO_DELETE (I_TXT_SESS_MESS);

create index IDX_XXX_SESSION_TO_DEL_PARAMS
on XXX_SESSION_TO_DELETE (I_TXT_SESS_PARAMS);

-- insiro na tabela criada as informações que preciso das sessões que quero excluir
insert into 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 SNP_SESSION
where sess_beg <>
commit;

analyze table XXX_SESSION_TO_DELETE compute statistics;
alter table XXX_SESSION_TO_DELETE enable row movement;


--faço a deleção de cada tabela de log de sessões de acordo com os registros que inseri na minha tabela auxiliar
delete from SNP_EXP_TXT where I_TXT in
(select I_TXT_TASK_MESS from SNP_SESS_TASK_LOG where SESS_NO in
(select SESS_NO from XXX_SESSION_TO_DELETE));
commit;

delete from SNP_EXP_TXT where I_TXT in
(select I_TXT_STEP_MESS from SNP_STEP_LOG where SESS_NO in
(select SESS_NO from XXX_SESSION_TO_DELETE));
commit;

delete from SNP_EXP_TXT where I_TXT in
(select I_TXT_VAR from SNP_VAR_SESS where SESS_NO in
(select SESS_NO from XXX_SESSION_TO_DELETE));
commit;

delete from SNP_EXP_TXT where I_TXT in
(select I_TXT_DEF_T from SNP_VAR_SESS where SESS_NO in
(select SESS_NO from XXX_SESSION_TO_DELETE));
commit;

delete from SNP_EXP_TXT where I_TXT in
(select I_TXT_SESS_PARAMS from XXX_SESSION_TO_DELETE);
commit;

delete from SNP_EXP_TXT where I_TXT in
(select I_TXT_SESS_MESS from XXX_SESSION_TO_DELETE);
commit;

delete from SNP_SESS_TXT_LOG where SESS_NO in
(select SESS_NO from XXX_SESSION_TO_DELETE);
commit;

delete from SNP_SESS_TASK_LOG where SESS_NO in
(select SESS_NO from XXX_SESSION_TO_DELETE);
commit;

delete from SNP_STEP_LOG where SESS_NO in
(select SESS_NO from XXX_SESSION_TO_DELETE);
commit;

delete from SNP_TASK_TXT where SESS_NO in
(select SESS_NO from XXX_SESSION_TO_DELETE);
commit;

delete from SNP_SESS_TASK where SESS_NO in
(select SESS_NO from XXX_SESSION_TO_DELETE);
commit;

delete from SNP_SESS_STEP where SESS_NO in
(select SESS_NO from XXX_SESSION_TO_DELETE);
commit;

delete from SNP_SEQ_SESS where SESS_NO in
(select SESS_NO from XXX_SESSION_TO_DELETE);
commit;

delete from SNP_VAR_SESS where SESS_NO in
(select SESS_NO from XXX_SESSION_TO_DELETE);
commit;

delete from SNP_SESSION where SESS_NO in
(select SESS_NO from XXX_SESSION_TO_DELETE);
commit;

delete from SNP_EXP_TXT where I_TXT in
(select I_TXT_SESS_MESS from SNP_SCEN_REPORT where SESS_BEG <>
commit;

delete from SNP_STEP_REPORT where STEP_BEG <>
commit;

delete from SNP_SCEN_REPORT where SESS_BEG <>
commit;


-- por fim executo o comando shrink para cada tabela para liberar espaço físico no banco
alter table SNP_EXP_TXT enable row movement;
alter table SNP_EXP_TXT shrink space;

alter table SNP_SESS_TXT_LOG enable row movement;
alter table SNP_SESS_TXT_LOG shrink space;

alter table SNP_SESS_TASK_LOG enable row movement;
alter table SNP_SESS_TASK_LOG shrink space;

alter table SNP_STEP_LOG enable row movement;
alter table SNP_STEP_LOG shrink space;

alter table SNP_TASK_TXT enable row movement;
alter table SNP_TASK_TXT shrink space;

alter table SNP_SESS_TASK enable row movement;
alter table SNP_SESS_TASK shrink space;

alter table SNP_SESS_STEP enable row movement;
alter table SNP_SESS_STEP shrink space;

alter table SNP_SEQ_SESS enable row movement;
alter table SNP_SEQ_SESS shrink space;

alter table SNP_VAR_SESS enable row movement;
alter table SNP_VAR_SESS shrink space;

alter table SNP_SESSION enable row movement;
alter table SNP_SESSION shrink space;

alter table SNP_STEP_REPORT enable row movement;
alter table SNP_STEP_REPORT shrink space;

alter table SNP_SCEN_REPORT enable row movement;
alter table SNP_SCEN_REPORT shrink space;

--sempre é bom recoletar as estatisticas de cada tabela :)
analyze table SNP_EXP_TXT compute statistics;
analyze table SNP_SESS_TXT_LOG compute statistics;
analyze table SNP_SESS_TASK_LOG compute statistics;
analyze table SNP_STEP_LOG compute statistics;
analyze table SNP_TASK_TXT compute statistics;
analyze table SNP_SESS_TASK compute statistics;
analyze table SNP_SESS_STEP compute statistics;
analyze table SNP_SEQ_SESS compute statistics;
analyze table SNP_VAR_SESS compute statistics;
analyze table SNP_SESSION compute statistics;
analyze table SNP_STEP_REPORT compute statistics;
analyze table SNP_SCEN_REPORT compute statistics;

--deleto minha tabela auxiliar
drop table XXX_SESSION_TO_DELETE purge;


Esse script eu achei no site abaixo, apenas adaptei uns poucos pontos para minha necessidade:
http://www.myoraclesupports.com/content/manually-deleting-odi-sessions-and-scenario-related-reports