enqueue_locks.sql

Most blocking lock detection scripts fail to consider that processes waiting for, but not yet holding, a lock can block other processes that need a conflicting lock on the same resource. To resolve such problems, it is essential to consider the order of waiters.

This script shows all the locks that are held or wanted for each resource, together with the number of seconds since the lock was granted or requested respectively, in order. This script is intended to supplement other blocking lock detection scripts such as Oracle's utllockt.sql.

 

 

 

column resource format a20
column sid format a4 justify right
break on resource

select /*+ ordered */
  l.type || '-' || l.id1 || '-' || l.id2  "RESOURCE",
  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
where
  s.sid = l.sid and
  b.paddr (+) = s.paddr
order by
  l.type || '-' || l.id1 || '-' || l.id2,
  sign(l.request),
  l.ctime desc