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?