Oracle object locked by a session (v$locked_object, dba_hist_active_sess_history)

Find out which session is currently locking (causing deadlock) a particular object:-

select a.sid, a.serial# from v$session a, v$locked_object b, dba_objects c
where b.object_id = c.object_id
and a.sid = b.session_id
and OBJECT_NAME='MY_TABLE';

Find out which session(s) have locked an object in the history:-

Step 1:-
Scan the active session history for your session which is blocked by another session

select * from dba_hist_active_sess_history
where blocking_session is not null and current_obj# IN
(select object_id from dba_objects where owner = 'MY_SCHEMA' --Enter the object owner here:
--and object_name = 'TEST1 --Enter the object name here,
);

Step 2:-
Find the SQL ID to see what the blocking session was exactly doing

SELECT * FROM dba_hist_active_sess_history
WHERE SESSION_ID = 1234 AND SESSION_SERIAL# = 12345
and
current_obj# IN
(select object_id from dba_objects where owner = 'MY_SCHEMA' --Enter the object owner here:
--and object_name = 'TEST1 --Enter the object name here,
);

Step 3:-
Check the sql_fulltext of the sql_id in v$sql.

select sql_fulltext from v$sql;

Based on the following sql I got from stackoverflow.

SELECT *
from gv$active_session_history
--Or use this table for further back.
--from dba_hist_active_sess_history
where blocking_session is not null
and current_obj# =
(
select object_id
from dba_objects
where owner = 'JHELLER' --Enter the object owner here.
and object_name = 'TEST1' --Enter the object name here.
);

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.