Locking

col OSUSER for a10
col MODE_HELD for a10
col MODE_REQUESTED for a10
col OWNER for a12
col object_type for a15
col object_name for a20
col MACHINE for a21
col LOCK_ID1 for a10
col LOCK_ID2 for a9
col LOCK_TYPE for a11
col lmode format 9999
col username for a13
col inst_id format 9
col sid format 99999

Select se.inst_id, lk.SID, decode(lk.Block, 0, 'No', 1, 'Yes', 2, 'Global') block ,se.username, TO_SINGLE_BYTE(se.OSUSER) as OSUSER, TO_SINGLE_BYTE(se.MACHINE) as MACHINE, DECODE (lk.TYPE, 'TX', 'Transaction', 'TM', 'DML', 'UL', 'PL/SQL User Lock', lk.TYPE) lock_type, lmode,DECODE (lk.lmode, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', TO_CHAR (lk.lmode)) mode_held, DECODE (lk.request, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', TO_CHAR (lk.request)) mode_requested, TO_CHAR (lk.id1) lock_id1, TO_CHAR (lk.id2) lock_id2, decode(lk.type, 'TM', ob.owner, null) owner, decode(lk.type, 'TM', ob.object_type, null) object_type, decode(lk.type, 'TM', ob.object_name, null) object_name
FROM GV$lock lk, dba_objects ob, GV$session se
WHERE lk.TYPE IN ('TX', 'TM', 'UL') AND lk.SID = se.SID AND lk.id1 = ob.object_id (+) AND lk.inst_id = se.inst_id
Order by block;