SDE limpa_conexoes_nao_ativas.sql

limpa_conexoes_nao_ativas.sql

select USERNAME, MACHINE, OSUSER, PROGRAM from v$session where audsid
in (select audsid from SDE.PROCESS_INFORMATION);

-- Identifica os usuários que estão na process information e não possuem conexão ativa no oracle
select * from sde.process_information
where audsid not in (select audsid from v$session);

-- Limpa as conexões orfãs
delete from sde.process_information
where audsid not in (select audsid from v$session);

-- Limpa as conexões orfãs da LAYER_LOCKS
delete from sde.LAYER_LOCKS
where sde_id not in (select sde_id from sde.process_information);

-- Limpa as conexões orfãs da OBJECT_LOCKS
delete from sde.OBJECT_LOCKS
where sde_id not in (select sde_id from sde.process_information);

-- Limpa as conexões orfãs da STATE_LOCKS
delete from sde.STATE_LOCKS
where sde_id not in (select sde_id from sde.process_information);

-- Limpa as conexões orfãs da TABLE_LOCKS
delete from sde.TABLE_LOCKS
where sde_id not in (select sde_id from sde.process_information);

commit;
/