Query to get deadlocked table and fired query during deadlock

Query to get deadlocked table:

select a.session_id,a.oracle_username, a.os_user_name, b.owner, b.object_name,b.object_type,a.locked_mode from
(select object_id, SESSION_ID, ORACLE_USERNAME, OS_USER_NAME, LOCKED_MODE from v$locked_object) a, (select object_id, owner, object_name,object_type from dba_objects) b
where a.object_id=b.object_id and a.oracle_username=’STDUIAPR29A’;

Query to get fired query during deadlock:

step1: get sid for deadlocked session:
select sid from v$lock where block>0;

step2: use above sid in followning query to get fired sql:

SELECT B.USERNAME,b.sid, B.MACHINE, B.TERMINAL, B.STATUS, A.SQL_FULLTEXT, A.SQL_TEXT
FROM V$SQLAREA A, V$SESSION B
WHERE A.HASH_VALUE=B.PREV_HASH_VALUE AND b.username=’STDUIAPR29A’ and sid=1531;

You can skip to the end and leave a response. Pinging is currently not allowed.

Leave a Reply