Show locks and locks waiting per session

/* Formatted on (Formatter Plus v4.8.8) */
COL uname head "Username" form a12
COL sid head "SID" form 999
COL ltype head "Type" form a4
COL lmode head "Mode" form a10
COL blocked head "Wait" form a4
COL details head "Details" form a40


SET verify off



ACCEPT user prompt "Username [%]: "



SELECT s.SID SID, s.username uname, 'DML' ltype,
DECODE (l.lmode,
1, 'Null',
2, 'Row-S',
3, 'Row-X',
4, 'Share',
5, 'S/Row-X',
6, 'Exclusive'
) lmode,
DECODE (l.request, 0, 'No', 'Yes') blocked,
u.NAME || '.' || o.NAME details
FROM v$session s, v$lock l, SYS.obj$ o, SYS.user$ u
WHERE s.username LIKE NVL (UPPER ('&user' || '%'), '%')
AND s.SID = l.SID
AND l.id1 = o.obj#
AND l.TYPE = 'TM'
AND o.owner# = u.user#(+)
UNION ALL
SELECT s.SID SID, s.username uname,
DECODE (l.TYPE, 'TX', 'TX', 'UL', 'USR', 'SYS') ltype,
DECODE (l.lmode,
1, 'Null',
2, 'Row-S',
3, 'Row-X',
4, 'Share',
5, 'S/Row-X',
6, 'Exclusive'
) lmode,
DECODE (l.request, 0, 'No', 'Yes') blocked,
DECODE (l.request,
0, NULL,
'Waiting on session ' || TO_CHAR (b.SID)
) details
FROM v$session s, v$lock l, v$lock b
WHERE s.username LIKE NVL (UPPER ('&user' || '%'), '%')
AND s.SID = l.SID
AND l.TYPE != 'TM'
AND l.id1 = b.id1(+)
AND b.request(+) = 0
ORDER BY 5 DESC, 3 DESC, 2, 1;



SET verify on