SQL ScriptMonitor All Database Locks |
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.
None.
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
Home
|
Company Profile
|
Services
|
Contact Us
|
SQL scripts and tips
|
Quiz
|