SELECT DISTINCT /*+ ordered */ w1.sid waiting_session,
h1.sid holding_session,
w.kgllktype lock_or_pin,
od.to_owner object_owner,
od.to_name object_name,
oc.TYPE,
decode(h.kgllkmod,
0,
'None',
1,
'Null',
2,
'Share',
3,
'Exclusive',
'Unknown') mode_held,
decode(w.kgllkreq,
0,
'None',
1,
'Null',
2,
'Share',
3,
'Exclusive',
'Unknown') mode_requested,
xw.kglnaobj wait_sql,
xh.kglnaobj hold_sql
FROM dba_kgllock w,
dba_kgllock h,
v$session w1,
v$session h1,
v$object_dependency od,
v$db_object_cache oc,
x$kgllk xw,
x$kgllk xh
WHERE (((h.kgllkmod != 0) AND (h.kgllkmod != 1) AND
((h.kgllkreq = 0) OR (h.kgllkreq = 1))) AND
(((w.kgllkmod = 0) OR (w.kgllkmod = 1)) AND
((w.kgllkreq != 0) AND (w.kgllkreq != 1))))
AND w.kgllktype = h.kgllktype
AND w.kgllkhdl = h.kgllkhdl
AND w.kgllkuse = w1.saddr
AND h.kgllkuse = h1.saddr
AND od.to_address = w.kgllkhdl
AND od.to_name = oc.NAME
AND od.to_owner = oc.owner
AND w1.sid = xw.kgllksnm
AND h1.sid = xh.kgllksnm
AND (w1.sql_address = xw.kglhdpar AND w1.sql_hash_value = xw.kglnahsh)
AND (h1.sql_address = xh.kglhdpar AND h1.sql_hash_value = xh.kglnahsh);