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');