how can i get the blocking lock statistics which were occurred several days back? i want to get the causing sql, user, objects locked etc... i have an awr report taken for that particular time period. im using oracle 11g r2.
thanks in advance.
I think you can start with "v$active_session_history" view.This can give you the sql_id,machine,user_id etc details.
Access to this table is subjected to a "Diagnostics & Tuning"-pack license.
If you don't have the license you're not allowed to use that View
Anyway this view only contains information of the last couple of hours
It then is flushed into dba_hist_active_sess_history which contains data for several days, but not at detailed as the v$active_session_history