Thursday, 29 January 2015

Another Oracle Lock Query

The lock queries already posted show the oldest lock and who is waiting on a lock. However often you just want a plain who has locks on a

SELECT do.object_name, do.object_type, vs.username, vs.osuser, vs.sid||','||vs.serial# sid_serial, ( CASE glo.locked_mode WHEN 0 THEN 'None' WHEN 1 THEN 'Null' WHEN 2 THEN 'Row-S (SS)' WHEN 3 THEN 'Row-X (SX)' WHEN 4 THEN 'Share' WHEN 5 THEN 'S/Row-X (SSX)' WHEN 6 THEN 'Exclusive' ELSE TO_CHAR(glo.locked_mode) END ) lock_mode, do.status object_status FROM dba_objects do, gv$locked_object glo, v$session vs WHERE do.object_id = glo.object_id AND glo.session_id = vs.sid ORDER BY 1,3

No comments: