oracle死锁查询

---查询谁锁了什么对象

SELECT /*+RULE*/

S.MACHINE,

S.OSUSER "O/S-USER",

https://www.360docs.net/doc/f8425902.html,ERNAME "ORA-USER",

S.SID "SESSION-ID",

S.SERIAL# "SERIAL",

S.PROCESS "PROCESS-ID",

S.STATUS "STATUS",

https://www.360docs.net/doc/f8425902.html, "OBJ LOCKED",

L.MODE_HELD "LOCK MODE",

S.LOGON_TIME

FROM V$SESSION S, DBA_DML_LOCKS L, V$PROCESS P

WHERE L.SESSION_ID = S.SID

AND P.ADDR = S.PADDR;

---查询谁锁了什么对象+执行的SQL

在数据库级别用alter system kill session 'sid,serial#';杀掉不正常的锁。SELECT /*+RULE*/

S.MACHINE,

S.OSUSER "O/S-USER",

https://www.360docs.net/doc/f8425902.html,ERNAME "ORA-USER",

S.SID "SESSION-ID",

S.SERIAL# "SERIAL",

S.PROCESS "PROCESS-ID",

S.STATUS "STATUS",

https://www.360docs.net/doc/f8425902.html, "OBJ LOCKED",

L.MODE_HELD "LOCK MODE",

S.LOGON_TIME,

A.SQL_TEXT,

A.SQL_FULLTEXT

FROM V$SESSION S, DBA_DML_LOCKS L, V$PROCESS P, V$SQLAREA A WHERE L.SESSION_ID = S.SID

AND P.ADDR = S.PADDR

AND A.ADDRESS = S.SQL_ADDRESS

AND A.HASH_VALUE = S.SQL_HASH_VALUE;

---通过SID查询执行的SQL：

SELECT sql_text

FROM v$sqlarea

WHERE (v$sqlarea.address, v$sqlarea.hash_value) IN

(SELECT sql_address, sql_hash_value FROM v$session WHERE SID = 1069); --通过SID生成KILL会话的SQL：

select 'alter system kill session ''' || t2.sid || ',' || t2.serial# ||''';'

from v$locked_object t1, v$session t2, dba_objects t3

where t1.session_id = t2.sid

and t1.object_id = t3.object_id

--and SID = 1069

order by t2.logon_time;

---查询会话之间的相会阻塞情况

select '阻塞者(' || https://www.360docs.net/doc/f8425902.html,ERNAME || '-' || sb.SID || ':' || sb.SERIAL# || ') SQL=' || qb.SQL_TEXT blockers, '等待者(' || https://www.360docs.net/doc/f8425902.html,ERNAME || '-' || sw.SID || ':' || sw.SERIAL# || ') SQL=' || qw.SQL_TEXT waiters

from v$lock lb, v$lock lw, v$session sb, v$session sw, v$sql qb, v$sql qw

where lb.SID = sb.SID

and lw.SID = sw.SID

and sb.PREV_SQL_ADDR = qb.ADDRESS

and sw.SQL_ADDRESS = qw.ADDRESS

and lb.ID1 = lw.ID1

and sb.LOCKWAIT is null

and sw.LOCKWAIT is not null

and lb.BLOCK = 1;