Find Blocking Sessions in Oracle Database

In this article I would like to describe how to check for blocking sessions within the Oracle DB in 11g. I used to use the dba_blockers view in Oracle 10g but that doesn’t seem to do the trick the same way as in 11. So, there is another way to find out which sessions are blocked.

It’s quite handy actually, because Oracle have now included it within the V$SESSION view to make it more accessible. There is a column in V$SESSION called blocking_session_status which has the value of VALID when that session is being blocked. If you take another column, blocking_session, for the session being blocked you will find out all of the information you need to identify the blocking session. Have a look at the SQL below.

How to find out which is the blocking session in oracle 11g:

select blocking_session,sid,serial#,username,
machine,program,process
from v$session where sid=(select blocking_session
                          from v$session
                          where blocking_session_status='VALID');

This gives you plenty of information to determine who is blocking who and resolve the issue.

Like it, share it...

Category: 11g