Is there a way to see the history of session blocks caused by table locking? I can view current blocking sessions through the session browser, but would like to see a history if its possible. Thanks!
I’m sorry that your question was not answered in a timely manner. The history of past wait events is available in DBA_HIST_ACTIVE_SESS_HISTORY but, of course, that needs a license for the Diagnostics Pack. If you’re not licensed for Diagnostics Pack, check out S-ASH (simulated ASH) at www.oraclerealworld.com/…/33-2.
If you are lucky and your ASH has content long enough that you need, here is the query:
SELECT distinct a.sql_id ,a.inst_id,a.blocking_session,a.blocking_session_serial#,a.user_id,s.sql_text,a.module
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
Then with sql_id (determined by instance with inst_id) find that SQL code.
Hope this helps.