oracle查看死锁方法
死锁是oracle比较常见的的问题，本文提供查询死锁的相关语句及解决方法
方法一：查出死锁语句
SELECT bs.username "Blocking User", bs.username "DB User",
ws.username "Waiting User", bs.SID "SID", ws.SID "WSID",
bs.serial# "Serial#", bs.sql_address "address",
bs.sql_hash_value "Sql hash", bs.program "Blocking App",
ws.program "Waiting App", bs.machine "Blocking Machine",
ws.machine "Waiting Machine", bs.osuser "Blocking OS User",
ws.osuser "Waiting OS User", bs.serial# "Serial#",
ws.serial# "WSerial#",
DECODE (wk.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 Invalidation',
'LS', 'LOG START OR Switch',
'RW', 'ROW Wait',
'SQ', 'Sequence Number',
'TE', 'Extend TABLE',
'TT', 'Temp TABLE',
wk.TYPE
) lock_type,
DECODE (hk.lmode,
0, 'None',
1, 'NULL',
2, 'ROW-S (SS)',
3, 'ROW-X (SX)',
4, 'SHARE',
5, 'S/ROW-X (SSX)',
6, 'EXCLUSIVE',
TO_CHAR (hk.lmode)
) mode_held,
DECODE (wk.request,
0, 'None',
1, 'NULL',
2, 'ROW-S (SS)',
3, 'ROW-X (SX)',
4, 'SHARE',
5, 'S/ROW-X (SSX)',
6, 'EXCLUSIVE',
TO_CHAR (wk.request)
) mode_requested,
TO_CHAR (hk.id1) lock_id1, TO_CHAR (hk.id2) lock_id2,
DECODE
(hk.BLOCK,
0, 'NOT Blocking', /**//* Not blocking any other processes */
1, 'Blocking', /**//* This lock blocks other processes */
2, 'Global', /**//* This lock is global, so we can't tell */
TO_CHAR (hk.BLOCK)
) blocking_others
FROM v$lock hk, v$session bs, v$lock wk, v$session ws
WHERE hk.BLOCK = 1
AND hk.lmode != 0
AND hk.lmode != 1
AND wk.request != 0
AND wk.TYPE(+) = hk.TYPE
AND wk.id1(+) = hk.id1
AND wk.id2(+) = hk.id2
AND hk.SID = bs.SID(+)
AND wk.SID = ws.SID(+)
AND (bs.username IS NOT NULL)
AND (bs.username <> 'SYSTEM')
AND (bs.username <> 'SYS')
ORDER BY 1;
查看死锁sid,serial,lockwait不为空则有锁表
SELECT distinct sid, serial#, username, lockwait,status,machine,program
FROM v$session a,
v$locked_object b
where a.sid = b.session_id and lockwait is not null
查看执行死锁的sql
select sql_text from v$sql a,v$session b,v$locked_object c
where a.hash_value = b.sql_hash_value and b.sid = c.session_id and lockwait is not null
kill session 语句，结束死锁线程
select distinct 'alter system kill session '|| ''''|| sess.sid || ',' || sess.serial# || ''''|| ';'
from v$locked_object lo,
dba_objects ao,
v$session sess
where ao.object_id = lo.object_id and lo.session_id = sess.sid and lockwait is not null ;
另外一个死锁查询方法
select s.sid,
s.username,
s.osuser,
s.program,
to_char(s.LOGON_TIME, 'yyyymmdd hh24:mi:ss') as LOGON_TIME,
to_char(t.START_DATE, 'yyyymmdd hh24:mi:ss') as START_DATE,
s.status,
(select q.SQL_TEXT from v$sql q where q.LAST_ACTIVE_TIME = t.START_DATE
and rownum <= 1) as SQL_TEXT
from v$session s,
v$transaction t
where s.sADDR = t.SES_ADDR;
