oracle 死锁

查询发生死锁的select语句

select sql_text from v$sql t1
inner join v$session t2
on t1.hash_value = t2.SQL_HASH_VALUE
inner join v$locked_object t3
on t2.SID = t3.session_id


是否有死锁
select username,lockwait,status,machine,program from v$session where sid in
(select session_id from v$locked_object)

1.Finding the Sessions Holding the Lock

Find the (ID1, ID2, type) for sessions waiting for a lock (LMODE=0).

Find the session holding the lock (REQUEST=0) for that ID1, ID2, type.

SELECT lpad(' ',DECODE(request,0,0,1))||sid sess, id1, id2, lmode, request, type
FROM V$LOCK
WHERE id1 IN (SELECT id1 FROM V$LOCK WHERE lmode = 0)
ORDER BY id1,request

SID          ID1        ID2      LMODE    REQUEST TY
------ ---------- ---------- ---------- ---------- --
1237       196705     200493          6          0 TX <- Lock Holder
1256      196705     200493          0          6 TX <- Lock Waiter
1176      196705     200493          0          6 TX <- Lock Waiter
938        589854     201352          6          0 TX <- Lock Holder
1634      589854     201352          0          6 TX <- Lock Waiter
2.Finding the Statements being Executed by These Sessions
SELECT sid,sql_address, sql_hash_value
FROM V$SESSION
WHERE SID IN (1237,1256,1176,938,1634);

SID  SQL_HASH_VALUE
-----  --------------
  938      2078523611 <-Holder
1176      1646972797 <-Waiter
1237      3735785744 <-Holder
1256      1141994875 <-Waiter
1634      2417993520 <-Waiter

3.Finding the Text for These SQL Statements
SELECT * FROM v$sqltext t
WHERE t.ADDRESS=''--代入上一步查到的sql_address
AND   t.HASH_VALUE=''--代入上一步查到的sql_hash_value
ORDER BY t.ADDRESS,t.HASH_VALUE,t.COMMAND_TYPE,t.PIECE;