SQL Script Database Deadlocks Locks

Updated : 3-November-1998
Version : 1.0

Description

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

Parameters

None.

SQL Source

col username form A15
col sid form 9990
col type form A4
col lmode form 990
col request form 990
col id1 form 9999990
col id2 form 9999990

break on id1 skip 1 dup
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,
         m.id1,m.id2
FROM v$session sn, V$lock m
WHERE (sn.sid = m.sid AND m.request != 0)
   OR (sn.sid = m.sid
      AND m.request = 0 AND lmode != 4
      AND (id1, id2 ) IN (SELECT s.id1, s.id2
                          FROM v$lock s
                          WHERE request != 0
                                 AND s.id1 = m.id1
                                 AND s.id2 = m.id2 )
      )
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 Legal