查询阻塞会话的语句与被阻塞的语句

刘泽宇

select distinct b.sql_id,b.sample_time,b.user_id,c.blocked_sql_id
from DBA_HIST_ACTIVE_SESS_HISTORY b,
(select a.sql_id as blocked_sql_id,
a.blocking_session,
a.blocking_session_serial#,
count(a.blocking_session)
from DBA_HIST_ACTIVE_SESS_HISTORY a
where event like '%enq: TX - row lock contention%'
and snap_id between snap_id1 and sanp_id2
group by a.blocking_session, a.blocking_session_serial#,a.sql_id
order by 3 desc) c
where b.session_id = c.blocking_session
and b.session_serial# = c.blocking_session_serial#
and b.snap_id between snap_id1 and snap_id2;