SELECT /*+ rule */
lpad(' ', decode(l.xidusn, 0, 3, 0)) || l.oracle_username User_name,
o.owner,
o.object_name,
o.object_type,
s.sid,
s.serial#
FROM v$locked_object l, dba_objects o, v$session s
WHERE l.object_id = o.object_id
AND l.session_id = s.sid
ORDER BY o.object_id, xidusn DESC;
SELECT DECODE(request, 0, 'Holder: ', 'Waiter: ') || sid sess,
id1,
id2,
lmode,
request,
type
FROM V$LOCK
WHERE (id1, id2, type) IN
(SELECT id1, id2, type FROM V$LOCK WHERE request > 0)
ORDER BY id1, request;