Find blocking session in Oracle Apps

Blocking Session Query for Oracle Apps

How to find the blocking sessions in oracle database and who is blocked by whom. Here user can find out the blocking session details like session id, user name, application user name, module , sql id, log on time etc.

You can modify the query as per business requirement. This is one of the most important query for a dba.

select sysdate,  

       vdb.name,

       blking.sid "Blocking DB Session ID", 

       blking.serial# "serial#",

       blking.status "Status",

       blking.client_identifier "Blocking eBS Username",

       blking_user_tab.description "Blocking User",

       blking.module "Blocking Module",

       blking.machine "Blocking OS Server",

       blking.logon_time "Blocking Session Logon Time",

       blking.sql_id "Blocking Current SQL_ID",

       blking.prev_sql_id "Blocking Past SQL_ID",

       blking.process "Blocking OS Process",

       blking.blocking_session "Nested Blocking DB Session ID",

       blkd.client_identifier "Blocked eBS Username",

       blkd_user_tab.description "Blocked User",

       blkd.sid "Blocked DB Session ID",

       blkd.module "Blocked Module",

       blkd.machine "Blocked Server",

       blkd.logon_time "Blocked Session Login",

       blkd.sql_id "Blocked Current SQL_ID",

       blkd.prev_sql_id "Blocked Past SQL_ID", 

       blkd.process "Blocked OS Process",

       BLKD.BLOCKING_SESSION "Session blocking"

from  

   v$session blkd, v$session blking, (select distinct a.client_identifier, b.description 

from   v$session a left join applsys.fnd_user b on a.client_identifier = b.user_name) blkd_user_tab, 

(select distinct a.client_identifier, b.description 

from   v$session a left join applsys.fnd_user b on a.client_identifier = b.user_name) blking_user_tab,v$database vdb

where 

   blkd.blocking_session is not NULL and

   blkd.blocking_session = blking.sid

    and blkd.client_identifier = blkd_user_tab.client_identifier 

    and blking.client_identifier = blking_user_tab.client_identifier

order by 

   blking.blocking_session,blking.logon_time;

Thank you for your time, hope this helps.