Verifica objetos com Locks

-- Verifica qual objeto está aguardando a liberação do Lock

select /*+ ordered */ w1.sid  waiting_session,     h1.sid  holding_session,
     w.kgllktype lock_or_pin,
        w.kgllkhdl address,
     decode(h.kgllkmod,  0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
           'Unknown') mode_held,
      decode(w.kgllkreq,  0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
        'Unknown') mode_requested  
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
/

- Mostra objeto que está em Lock

select distinct to_name from v$object_dependency where to_address = '&Adress';

select distinct kglnaobj from x$kgllk  where  kgllkuse in (select saddr from v$session where sid = &sid_WAITING_SESSION);

select distinct kglnaobj from x$kgllk  where  kgllkuse in (select saddr from v$session where sid = sid_HOLDING_SESSION);


set term on;
set lines 150;
set linesize 255;
column sid_ser format a12 heading 'Session,|Serial#';
column username format a30 heading 'OS User/|DB User';
column spid format a7 heading 'OS|Process';
column owner_object format a35 heading 'Owner.Object';
column locked_mode format a13 heading 'Locked|Mode';
column status format a8 heading 'Status';
column logon format a18 heading 'Date|Logon';

select
    substr(to_char(l.session_id)||','||to_char(s.serial#),1,12) sid_ser,
    substr(l.os_user_name||'/'||l.oracle_username,1,12) username,
    p.spid,
    substr(o.owner||'.'||o.object_name,1,35) Owner_object,
    decode(l.locked_mode,
             1,'No Lock',
             2,'Row Share',
             3,'Row Exclusive',
             4,'Share',
             5,'Share Row Excl',
             6,'Exclusive',null) Locked_mode,
    substr(s.status,1,8) Status,
    to_char(s.LOGON_TIME,'dd/mm/yyyy hh24:mi') logon
from
    v$locked_object l,
    all_objects     o,
    v$session       s,
    v$process       p
where
    l.object_id = o.object_id
and l.session_id = s.sid
and s.paddr      = p.addr
/

#####################################
#     Para ver o sql de um lock     #
#####################################

set pagesize 60
set linesize 132
select s.username username, 
       a.sid sid, 
       a.owner||'.'||a.object object, 
       s.lockwait, 
       t.sql_text SQL
from   v$sqltext t, 
       v$session s, 
       v$access a
where  t.address=s.sql_address 
and    t.hash_value=s.sql_hash_value 
and    s.sid = a.sid 
and    a.owner != 'SYS'
and    upper(substr(a.object,1,2)) != 'V$'
/


##############################################
#    Para ver quem esta aguardando um lock   #
##############################################

column username format  A15
column sid      format  9990    heading SID
column type     format  A4
column lmode    format  990     heading 'HELD'
column request  format  990     heading 'REQ'
column id1      format  9999990
column id2 format  9999990
break on id1 skip 1 dup
spool tfslckwt.lst
SELECT sn.username, m.sid, m.type,
        DECODE(m.lmode, 0, 'None',
                        1, 'Null',
                        2, 'Row Share',
                        3, 'Row Excl.',
                        4, 'Share',
                        5, 'S/Row Excl.',
                        6, 'Exclusive',
                lmode, ltrim(to_char(lmode,'990'))) lmode,
        DECODE(m.request,0, 'None',
                         1, 'Null',
                         2, 'Row Share',
                         3, 'Row Excl.',
                         4, 'Share',
                         5, 'S/Row Excl.',
                         6, 'Exclusive',
                         request, ltrim(to_char(m.request,
                '990'))) request, m.id1, m.id2
FROM v$session sn, v$lock m
WHERE (sn.sid = m.sid AND m.request != 0)
        OR (sn.sid = m.sid
                AND m.request = 0 AND lmode != 4
                AND (id1, id2) IN (SELECT s.id1, s.id2
     FROM v$lock s
                        WHERE request != 0
              AND s.id1 = m.id1
                                AND s.id2 = m.id2)
                )
ORDER BY id1, id2, m.request;
spool off
clear breaks