Doc

     Name:   lockhldr.sql

     Author: Mark Gurry

     This script lists the users that are currently holding locks that
     are currently blocking users as well as the users that are being 
     blocked. It also lists the offending objects (tables, etc). 

#

set heading on  
ttitle 'User Blocking and Waiting for Other Users'
select distinct o.object_name, sh.username||'('||sh.sid||')' "Holder", sw.username||'('||sw.sid||')' "Waiter",
        decode(lh.lmode, 1, 'null', 2, 
              'row share', 3, 'row exclusive', 4,  'share', 
              5, 'share row exclusive' , 6, 'exclusive')  "Lock Type"
  from all_objects o, v$session sw, v$lock lw, v$session sh, v$lock lh
 where lh.id1  = o.object_id
  and  lh.id1  = lw.id1
  and  sh.sid  = lh.sid
  and  sw.sid  = lw.sid
  and  sh.lockwait is null
  and  sw.lockwait is not null
  and  lh.type = 'TM'
  and  lw.type = 'TM'
/