SQL Script Monitor All Database Locks

Updated: 03-Nov-1998
Version: 1.0

Description

This script lists details of all current database locks on the system, who is holding the lock, and the object name where the lock is held.

Parameters

None

SQL Source

REM Copyright (C) Think Forward.com 1998- 2005. All rights reserved. 

set pages 20
col username form A10
col sid form 9990
col type form A4
col lmode form 990
col request form 990
col objname form A15 Heading "Object Name"
rem Display the object id's if the object_name is not unique
rem col id1 form 999999900   
rem col id2 form 999999900

spool check_lock.lis
SELECT sn.username, m.sid, m.type,
   DECODE(m.lmode, 0, 'None'
                 , 1, 'Null'
                 , 2, 'Row Share'
                 , 3, 'Row Excl.'
                 , 4, 'Share'
                 , 5, 'S/Row Excl.'
                 , 6, 'Exclusive'
                 , lmode, ltrim(to_char(lmode,'990'))) lmode,
   DECODE(m.request, 0, 'None'
                 , 1, 'Null'
                 , 2, 'Row Share'
                 , 3, 'Row Excl.'
                 , 4, 'Share'
                 , 5, 'S/Row Excl.'
                 , 6, 'Exclusive'
                 , request, ltrim(to_char(request,'990'))) request,
         obj1.object_name objname, obj2.object_name objname
FROM v$session sn, V$lock m, dba_objects obj1, dba_objects obj2
WHERE sn.sid = m.sid
AND m.id1 = obj1.object_id (+)
AND m.id2 = obj2.object_id (+)
     AND lmode != 4 
ORDER BY id1,id2, m.request
/
spool off
clear breaks

Return to Index of SQL Scripts

Site Map

General Information : Home | Company Profile | Contact Us | Legal
Database Support : Proactive Support | Pay Per Incident Support
Expert Services : High Availability | Creative Solutions | Training
Build Services : Oracle Installations | Revive your Oracle Setup | Low Cost Development
Free Oracle Resources: DBA Scripts | Oracle Links