Objetos em Lock - Oracle

Aqui vai um script que para identificar os objetos que estão em lock no banco:

OBS.: Para executar este script é necessario estar logado com o usuario SYS.

– Busca objeto em lock

select
distinct to_name object_locked
from
v$object_dependency
where
to_address in
(
select
w.kgllkhdl address
from
dba_kgllock w,
dba_kgllock h,
v$session w1,
v$session h1
where
(((h.kgllkmod != 0) and (h.kgllkmod != 1)
and ((h.kgllkreq = 0) or (h.kgllkreq = 1)))
and
(((w.kgllkmod = 0) or (w.kgllkmod= 1))
and ((w.kgllkreq != 0) and (w.kgllkreq != 1))))
and w.kgllktype = h.kgllktype
and w.kgllkhdl = h.kgllkhdl
and w.kgllkuse = w1.saddr
and h.kgllkuse = h1.saddr
)

– Busca sessoes que estao esperando liberar tabela. Traz database, usuario, tabela de espera, status sessao, maquina, aplicativo, hora logon

SELECT LPAD(‘ ‘, (level-1)*2, ‘ ‘) || NVL(s.username, ‘(oracle)’) AS username,
s.osuser,
s.sid,
s.serial#,
s.lockwait,
s.status,
s.module,
s.machine,
s.program,
TO_CHAR(s.logon_Time,’DD-MON-YYYY HH24:MI:SS’) AS logon_time
FROM v$session s
CONNECT BY PRIOR s.sid = s.blocking_session
START WITH s.blocking_session IS NULL;

—- Busca Tabelas que estao em uso, traz sessao, usuario, terminal, tabela

select lc.sid, s.serial#, ob.owner, ob.object_name TABELA_LOCADA, s.USERNAME, s.OSUSER, s.terminal
from v$lock lc, dba_objects ob, v$session s
where lc.ID1 = ob.object_id and
lc.SID = s.SID
AND S.OSUSER<>'SYSTEM'

-- Autor: André Weber --