--=============================================================================
--
--
--
--
-- GRANT DROP ANY SEQUENCE TO
-- GRANT SELECT ON SYS.V_$SESSION TO
--
-- The main purpose of this procedure and JOB is to DROP the "temporary tables"
-- which are no more used by the programs.
-- Programs are responsible of temporary table creation and destruction.
-- Programs should normally "unregister" temporary tables which are dropped,
-- but this might not happen if the program crashes.
--
-- Observation:
-- Can use this script only after technology TOTVS Tec - BUILD 2011.
--
-- ]]>
--
--
--=============================================================================
--
-- Properly modify the connection string.
--
--connect logix;
create or replace trigger drop_temp_table_by_session
before logoff on schema
begin
for c in (select table_name
from user_tables
where tablespace_name IS NULL
and table_name like 'T'||SubStr(LPad(UserEnv('SESSIONID'),38,'0'),-6,6)||'%') loop
-- Drop the table
begin
execute immediate 'drop table '||c.table_name||' purge';
exception
when others then null;
end;
-- Drop the sequence ( serial column )
begin
execute immediate 'drop sequence '||c.table_name||'_srl';
exception
when others then null;
end;
end loop;
exception
when others then raise;
end;
/
--
create or replace procedure drop_orphan_tables_by_session
is
begin
-- utilizada a função LENGTH(TRIM(TRANSLATE('', ' +-.0123456789', ' '))) is null
-- para testar se a sequencia de caracteres que compoe parte do nome dos objetos é um número, para não
-- eliminar outros objetos que não sejam tabelas temporárias
-- drop the table like T%
for c in (select t.table_name
from user_tables t
where t.table_name like 'T%'
and LENGTH(TRIM(TRANSLATE(substr(t.table_name,2,6), ' +-.0123456789', ' '))) is null
and t.tablespace_name IS NULL
and not exists(select 1
from v_sessao_ora v
where SubStr(LPad(v.sid,38,'0'),-6,6) = substr(t.table_name,2,6)) ) LOOP
begin
execute immediate 'drop table '||c.table_name||' purge';
exception
when others then null;
end;
end loop;
-- drop the table like TT%
for c in (select t.table_name
from user_tables t
where t.table_name like 'TT%'
and t.tablespace_name = 'TEMPTABS'
and LENGTH(TRIM(TRANSLATE(substr(t.table_name,3,5), ' +-.0123456789', ' '))) is null
and not exists(select 1
from v_sessao_ora v
where SubStr(LPad(v.sid,38,'0'),-5,5) = substr(t.table_name,3,5)) ) LOOP
begin
execute immediate 'drop table '||c.table_name||' purge';
exception
when others then null;
end;
end loop;
-- drop the sequence like T% ( serial column )
for c in (select s.sequence_name
from user_sequences s
where s.sequence_name like 'T%'
and LENGTH(TRIM(TRANSLATE(substr(s.sequence_name,2,6), ' +-.0123456789', ' '))) is null
and not exists(select 1
from v_sessao_ora v
where SubStr(LPad(v.sid,38,'0'),-6,6) = substr(s.sequence_name,2,6)) ) LOOP
begin
execute immediate 'drop sequence '||c.sequence_name;
exception
when others then null;
end;
end loop;
-- drop the sequence like TT% ( serial column )
for c in (select s.sequence_name
from user_sequences s
where s.sequence_name like 'TT%'
and LENGTH(TRIM(TRANSLATE(substr(s.sequence_name,3,5), ' +-.0123456789', ' '))) is null
and not exists(select 1
from v_sessao_ora v
where SubStr(LPad(v.sid,38,'0'),-5,5) = substr(s.sequence_name,3,5)) ) LOOP
begin
execute immediate 'drop sequence '||c.sequence_name;
exception
when others then null;
end;
end loop;
exception
when others then null;
end;
/
--
-- Properly modify the connection string. Attention to the owner of the procedure DROP_ORPHAN_TABLES_BY_SESSION.
-- Modify it also in the parameter job_action.
--
connect SYS as SYSDBA;
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'LOGIX.T_DROP_ORPHAN_TABS_BY_SESSION'
,job_type => 'PLSQL_BLOCK'
,job_action => 'LOGIX.DROP_ORPHAN_TABLES_BY_SESSION;'
,start_date => sysdate
,repeat_interval => 'FREQ=DAILY;INTERVAL=2;BYHOUR=23;BYMINUTE=0;BYSECOND=0'
,enabled => TRUE
,comments => 'Remove tabelas temporarias do LOGIX'
);
END;