SELECT DECODE(l.request, 0, 'Holder: ', 'Waiter: ') || l.sid sess,
s.SQL_HASH_VALUE,
l.id1,
l.id2,
l.lmode,
l.request,
l.type
FROM V$LOCK l, v$session s
WHERE (l.id1, l.id2, l.type) IN
(SELECT id1, id2, type FROM V$LOCK WHERE request > 0)
and l.SID = s.SID
and exists (select sw.seq#,
sw.sid || ',' || s.serial# sids,
s.username,
s.SQL_HASH_VALUE
from v$session_wait sw, v$session s, dba_objects a
where sw.sid = s.sid
and sw.event in ('latch free', 'enqueue')
and s.status = 'ACTIVE'
and sw.STATE = 'WAITING'
and s.username is not null
and sw.seconds_in_wait > 30
and (a.object_id = s.row_wait_obj# or
a.data_object_id = s.row_wait_obj#))
ORDER BY l.id1, l.request;