I am looking for a query which detects the deadlocks created by the users. I tried with few handy queries but i am able to capture the blocking sessions..
My requirement is i need only the deadlocks to get captured.
Tried few of the below queries :
SQL> SELECT (
SELECT username
FROM gv$session
WHERE sid=a.sid) blocker,
a.sid, ' is blocking ', (
SELECT username
FROM gv$session
WHERE sid=b.sid) blockee,
b.sid
FROM gv$lock a, gv$lock b
WHERE a.block = 1
AND b.request > 0
AND a.id1 = b.id1
AND a.id2 = b.id2; 2 3 4 5 6 7 8 9 10 11 12 13 14
BLOCKER SID 'ISBLOCKING'
------------------------------ ---------- -------------
BLOCKEE SID
------------------------------ ----------
SYS 1705 is blocking
SYS 430
===========
SELECT TO_CHAR(sysdate, 'DD-MON-YYYY HH24:MI:SS')
|| ' User '
||s1.username
|| '@'
|| s1.machine
|| ' ( SID= '
|| s1.sid
|| ' ) with the statement: '
|| sqlt2.sql_text
||' is blocking the SQL statement on '
|| s2.username
|| '@'
|| s2.machine
|| ' ( SID='
|| s2.sid
|| ' ) blocked SQL -> '
||sqlt1.sql_text AS blocking_status
FROM v$lock l1, v$session s1, v$lock l2 ,
v$session s2,v$sql sqlt1, v$sql sqlt2
WHERE s1.sid =l1.sid
AND s2.sid =l2.sid AND sqlt1.sql_id= s2.sql_id AND sqlt2.sql_id= s1.prev_sql_id AND l1.BLOCK =1
AND l2.request > 0 AND l1.id1 = l2.id1 AND l2.id2 = l2.id2
/
The o/p for the above query is
no rows selected
SQL>
But i have created the deadlock and this i could find from other query as given the details below :
SQL> select sql_text from v$sqltext where (address,hash_value) in (select sql_address,sql_hash_value from v$session where lockwait is not null) order by address, hash_value, piece;
SQL_TEXT
----------------------------------------------------------------
select num,txt from New for update of txt
I need the user details as to who is the blocker and who is the waiter along with the SQL text for the blocker and waiter.
Could anyone help with this please..?
Thanks,
Sindhu
Changed Discussion heading name to be more specific. Message was edited by: CKPT