Check historical blocking session in Oracle

Check the last 24 hours blocking history of session 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 >= SYSDATE - 1;


Check total blocking history of session 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


Fetch for particular user blocking 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 in (select user_id from dba_users where username = 'SCOTT');