waiter.sql

select SUBSTR(S1.username,1,12) "WAITING User",
       SUBSTR(s1.osuser,1,8) "OS User",
       SUBSTR(to_char(w.session_id),1,5) "Sid",
       p1.spid "PID",
       SUBSTR(S2.username,1,12) "HOLDING User",
       SUBSTR(s2.osuser,1,8) "OS User",
       SUBSTR(to_char(h.session_id),1,5) "Sid",
       p2.spid "PI"
from sys.v_$process p1, 
     sys.v_$process p2,
     sys.v_$session s1,
     sys.v_$session s2,
     dba_locks w, 
     dba_locks h
where h.mode_held = 'None'
  and h.mode_held = 'Null'
  and w.mode_requested != 'None'
  and w.lock_type (+) = h.lock_type
  and w.lock_id1 (+) = h.lock_id1
  and w.lock_id2 (+) = h.lock_id2
  and w.session_id = s1.sid (+)
  and h.session_id = s2.sid (+)
  and s1.paddr = p1.addr (+)
  and s2.paddr = p2.addr (+)

/