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',

1, 'Null',

2,

'Row-S',

3, 'Row-X',

4, 'Share',

5, 'S/Row-X',

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;

或

select /*+RULE*/

'alter system kill session ''' || a.sid || ','

|| b.serial# || ''';'

from v$lock a, v$session b

where a.sid > 8

and a.sid = b.sid;

3、把第二步的查询结果导出为脚本，在SQLPLUS中执行该脚本即可解除死锁。