ORACLE查找并解除死鎖程序

  ORACLE查找并解除死鎖程序

1、查找死鎖程序

select /*+RULE*/v$lock.sid,

decode(v$lock.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 Invalida-tion',

        'LS', 'Log Start or Switch',

        'RW', 'Row Wait',

        'SQ', 'Sequence Number',

        'TE', 'Extend Table',

        'TT', 'Temp Table',

        'Unknown') LockType,

rtrim(object_type) || ' ' || rtrim(owner) || '.' || object_name object_name,

decode(lmode,   0, 'None',

                1, 'Null',

                2, 'Row-S',

                3, 'Row-X',

                4, 'Share',

                5, 'S/Row-X',

                6, 'Exclusive',        'Unknown') LockMode,

decode(request, 0, 'None',

                6, 'Exclusive', 'Unknown') RequestMode,

ctime, block b

from v$lock, all_objects

where sid > 8

and v$lock.id1 = all_objects.object_id

如果查詢結果有記錄表明有鎖

2、這個SQL可用來生成殺死死鎖程序的SQL

select /*+RULE*/

 'alter system kill session ''' || a.sid || ',' || b.serial# || ''';'

 from v$lock a, all_objects c, v$session b

 where a.sid > 8

   and a.id1 = c.object_id

   and a.sid = b.sid;

或

 from v$lock a, v$session b

3、把第二步的查詢結果導出為腳本，在SQLPLUS中執行該腳本即可解除死鎖。