oracle查询历史会话,查看历史会话等待事件对应的session信息

DBA_HIST_ACTIVE_SESS_HISTORY中的session_id找到对应的sql_id从而得到回话信息。

可以通过如下查询直接获取信息：

select t.instance_number,

t.sample_time,

lpad('-', 2 * (level - 1), '-') || t.client_id,

t.session_id,

t.blocking_session,

t.session_serial#,

t.sql_id,

t.event,

t.session_state,

level,

connect_by_isleaf,

connect_by_iscycle

from dba_hist_active_sess_history  t

where snap_id between 36878 and 36879

start with blocking_session is not null

and event like 'enq: TX - row lock contention%'

connect by nocycle sample_time = prior sample_time

and session_id = prior blocking_session

and session_serial# = prior blocking_session_serial#

其中blocking session为正在阻塞该回话的session

实战案例：

查看等待事件为行锁的session

select a.snap_id,

a.sql_