--Program : showlocks.sql
--Purpose : Displays useful information about the locks held by a given session.
--Usage : SQL>@showlocks.sql
--Notes : Prompts for a sid. If called from sessinfo.sql it uses the sid value as defined in sessinfo
--Example : SQL>@showlocks
-- : SQL>@sessinfo
-- ....
-- Enter value for sid: 131
--
-- [ invokes showlocks automatically -- remember to remove the comments from sessinfo.sql]
--
--Author : Ram Razdan , ramrazdan@yahoo.com
--License : Free for public use, amendment and distribution.
-- Please refer https://ramrazdan.tripod.com/termsofuse.txt for full details.
--Revision : Version Date Server/OS Comments
-- 1.0 Dec,2000 Oracle7.1/VM-CMS Initial Revision.
-- 1.1 Jan,2001 Oracle7.3/SunOS Add query to show the name of the objects locked.
-- 1.2 Apr,2001 Oracle8.1/Win98 Testing Only.
--
COLUMN "Lock Type" FORMAT A30
COLUMN "Mode Locked" FORMAT A16
COLUMN "Mode Requested" FORMAT A16
SELECT type||' '||DECODE(type
,'MR', 'Media Recovery'
,'RT', 'Redo Thread'
,'UN', 'User Name'
,'TX', 'Transaction'
,'TM', 'DML'
,'UL', 'PL/SQL User Lock'
,'DX', 'Distributed Xaction'
,'CF', 'Control File'
,'IS', 'Instance State'
,'FS', 'File Set'
,'IR', 'Instance Recovery'
,'ST', 'Disk Space Transaction'
,'TS', 'Temp Segment'
,'IV', 'Library Cache Invalidation'
,'LS', 'Log Start or Switch'
,'RW', 'Row Wait'
,'SQ', 'Sequence Number'
,'TE', 'Extend Table'
,'TT', 'Temp Table'
) "Lock Type"
,DECODE(request
,0, '0 None'
,1, '1 Null'
,2, '2 Row-S (SS)'
,3, '3 Row-X (SX)'
,4, '4 Share'
,5, '5 S/Row-X (SSX)'
,6, '6 Exclusive'
) "Mode Requested"
,DECODE(lmode
,0, '0 None'
,1, '1 Null'
,2, '2 Row-S (SS)'
,3, '3 Row-X (SX)'
,4, '4 Share'
,5, '5 S/Row-X (SSX)'
,6, '6 Exclusive'
) "Mode Locked"
,ctime "Current Mode Time"
,block "Blocking Locks"
,id1
FROM v$lock
WHERE sid = &sid
/
COLUMN "Locked By" FORMAT A40
COLUMN "Object" FORMAT A40
SELECT ll.object_id
,oo.owner||'.'||oo.object_name "Object"
,ll.oracle_username||'-'||ll.os_user_name||'-'||ll.process "Locked By"
,DECODE(ll.locked_mode
,0, '0 None'
,1, '1 Null'
,2, '2 Row-S (SS)'
,3, '3 Row-X (SX)'
,4, '4 Share'
,5, '5 S/Row-X (SSX)'
,6, '6 Exclusive'
) "Mode Locked"
FROM v$locked_object ll
,all_objects oo
WHERE ll.session_id = &sid
AND ll.object_id = oo.object_id
/
UNDEFINE sid