Query to find Blocking Session History in Oracle

      
SELECT DISTINCT a.sql_id,
                a.inst_id,
                a.blocking_session         blocker_ses,
                a.blocking_session_serial# blocker_ser,
                a.user_id,
                s.sql_text,
                a.module,
                a.sample_time

  FROM GV$ACTIVE_SESSION_HISTORY a, gv$sql s
 WHERE     a.sql_id = s.sql_id
       AND blocking_session IS NOT NULL
       AND a.user_id <> 0                                  -- exclude SYS user
    --   AND a.sample_time BETWEEN SYSDATE - 1 AND SYSDATE - 23 / 24
    ;

 

       


SELECT  distinct a.sql_id, a.blocking_session,a.blocking_session_serial#,
a.user_id,s.sql_text,a.module
FROM  V$ACTIVE_SESSION_HISTORY a, v$sql s
where a.sql_id=s.sql_id
and blocking_session is not null
and a.user_id <> 0
--and a.sample_time between to_date('17/06/2011 00:00', 'dd/mm/yyyy hh24:mi')  and to_date('17/06/2011 23:50', 'dd/mm/yyyy hh24:mi');

       
 

       


SELECT distinct a.sql_id ,a.inst_id,a.blocking_session,a.blocking_session_serial#,a.user_id,s.sql_text,a.module,a.*
FROM 
GV$ACTIVE_SESSION_HISTORY a ,gv$sql s
where a.sql_id=s.sql_id
and blocking_session is not null
and a.user_id <> 0 -- exclude SYS user
 and a.sample_time > sysdate - 7