How to view all currently locked objects in database

SELECT username  U_NAME, owner OBJ_OWNER,
object_name, object_type, s.osuser,
DECODE(l.block,
0,  ‘Not Blocking’,
1,  ‘Blocking’,
2,  ‘Global’) STATUS,
DECODE(v.locked_mode,
0,  ‘None’,
1,  ‘Null’,
2,  ‘Row-S (SS)’,
3,  ‘Row-X (SX)’,
4,  ‘Share’,
5,  ‘S/Row-X (SSX)’,
6,  ‘Exclusive’, TO_CHAR(lmode)
) MODE_HELD
FROM gv$locked_object v, dba_objects d,
gv$lock l, gv$session s
WHERE v.object_id = d.object_id
AND (v.object_id = l.id1)
AND  v.session_id = s.sid
ORDER BY username, session_id;

2 thoughts on “How to view all currently locked objects in database”

Leave a Reply

Your email address will not be published. Required fields are marked *