SQL Query to Find Blocking Sessions and SQL Text for Last One Day from History

In this post I wanted to give you one sql script to find blocking sessions for the last one day from the history.

SQL Query

set pages 1000 lines 222

col sql_id for a17
col inst_id for '99'
col sql_text for a60col module format a10
col blocker_sid format '9999999'
col blocker_ser# format '9999999'
  
SELECT distinct
        a.sql_id ,
        to_char(a.sql_exec_start,'DD-Mon HH24:MI') sql_start,
        a.inst_id,
        a.module,
        a.blocking_session blocker_sid,
        a.blocking_session_serial# blocker_ser#,
        a.user_id,
        s.sql_text        
 FROM  GV$ACTIVE_SESSION_HISTORY a,
       gv$sql s
 where a.user_id <> 0
 and   a.sql_id=s.sql_id
   and blocking_session is not null
   and a.sample_time > sysdate - 1
 order by sql_start 

The condition user_id!=0 is used for excluding SYS user sessions