Query to check blocking sessions

 
Here’s a query I use to check for blocking sessions on the database.
A deadlock is composed of 2 sessions blocking each other (2-way blocks) and a single blocking session can bock several other sessions. Deadlocks are resolved automatically by rolling back the statement associated with the transaction that detects the deadlock. Whereas a single blocking session can block several other sessions and is not released until the session has completed the transaction.

select sess1.username || '@' || sess1.machine
|| ' ( SID=' || sess1.sid || ' ) is blocking '
|| sess2.username || '@' || sess2.machine || ' ( SID=' || sess2.sid || ' ) ' AS blocking_status
from v$lock lock1,
v$session sess1,
v$lock lock2,
v$session sess2
where sess1.sid=lock1.sid
and sess2.sid=lock2.sid
and lock1.BLOCK=1
and lock2.request > 0
and lock1.id1 = lock2.id1
and lock2.id2 = lock2.id2;

Replace V$ with GV$ if you’re on a RAC database.

select obj.owner,
obj.object_name,
lok.oracle_username,
lok.os_user_name,
lok.process
from V$LOCKED_OBJECT lok,
ALL_OBJECTS obj
where lok.object_id = obj.object_id;

select obj.owner,
obj.object_name,
lok.oracle_username,
lok.os_user_name,
lok.process
from V$LOCKED_OBJECT lok,
ALL_OBJECTS obj
where obj.object_name = '&table_name';

If you need more help with database issues, feel free to send me an email (jimmycdo@yahoo.com) and I'll gladly help.

About Jimbart