Script - Enqueue_locks

Para ver los bloqueos actuales y los requeridos/pendientes/encolados.

column resource format a20
column object format a30
column type format a10
column holding format a10
column wanting format a10
column sid format a4 justify right

break on resource
select /*+ ordered */
  l.type || '-' || l.id1 || '-' || l.id2  "RESOURCE",o.object_name "OBJECT", o.object_type "TYPE",
  nvl(b.name, lpad(to_char(l.sid), 4))  sid,
  decode(
    l.lmode,
    1, '      N',
    2, '     SS',
    3, '     SX',
    4, '      S',
    5, '    SSX',
    6, '      X'
  )  holding,
  decode(
    l.request,
    1, '      N',
    2, '     SS',
    3, '     SX',
    4, '      S',
    5, '    SSX',
    6, '      X'
  )  wanting,
  l.ctime  seconds
from
  sys.v_$lock l,
  sys.v_$session s,
  sys.v_$bgprocess b,
    (select object_name, object_type, object_id from dba_objects ) o
where
  s.sid = l.sid and
  s.sid not in (2,6,8) and
  b.paddr (+) = s.paddr and
  l.id1=o.object_id(+)
order by
  l.type || '-' || l.id1 || '-' || l.id2,
  sign(l.request),
  l.ctime desc
/