Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

find sessions blocked by the blocking sessions in history

MoazzamApr 30 2013 — edited Apr 30 2013
We are using Oracle 10g R2 on Linux. I am using following query to get all the blocking sessions in the last 7 days:
SELECT  min(A.SAMPLE_TIME) start_time,max(A.SAMPLE_TIME) end_time,a.inst_id,a.blocking_session,a.user_id,s.sql_text,A.EVENT,O.OBJECT_NAME,max(A.SAMPLE_TIME) - min(A.SAMPLE_TIME)  
FROM  GV$ACTIVE_SESSION_HISTORY a  ,gv$sql s, dba_objects o
where a.sql_id=s.sql_id
and A.CURRENT_OBJ# = O.OBJECT_ID
and blocking_session is not null
and a.user_id <> 0 -- exclude SYS user 
and a.sample_time > sysdate - 7 
and a.event = 'enq: TX - row lock contention'
group by a.inst_id,a.blocking_session,a.user_id,s.sql_text,A.EVENT,O.OBJECT_NAME
I also want to get the sessions that were blocked by these blocking sessions. Can you please guide me?
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 28 2013
Added on Apr 30 2013
1 comment
3,241 views