doc

    Name:    blocker.sql

    Author:  Mark Gurry

    This script shows who is holding a lock that other people are
    waiting for and who the waiters are.

#

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'
/