如何在Oracle历史视图查询中获取应用服务器的OS用户信息？

DBA_HIST_ACTIVE_SESS_HISTORY（ASH历史视图）确实未存储OS用户信息，但Oracle提供的DBA_HIST_SESSION视图是AWR捕获的会话快照历史，包含OSUSER字段，可通过关联该视图补充原有查询的OS用户数据。

核心关联逻辑 #

DBA_HIST_ACTIVE_SESS_HISTORY与DBA_HIST_SESSION可通过以下唯一标识字段关联，避免会话复用导致的匹配错误：

• DBID：数据库ID
• INSTANCE_NUMBER：实例编号
• SNAP_ID：AWR快照ID
• SESSION_ID + SESSION_SERIAL#：会话唯一标识组合

示例查询 #

假设原有查询结构如下：

SELECT
    ash.sample_time,
    u.username,
    sql.sql_text
FROM
    DBA_HIST_ACTIVE_SESS_HISTORY ash
JOIN DBA_USERS u ON ash.user_id = u.user_id
JOIN DBA_HIST_SQLTEXT sql ON ash.sql_id = sql.sql_id
WHERE ash.sample_time BETWEEN TO_DATE('2024-01-01', 'YYYY-MM-DD') AND SYSDATE;

修改后加入DBA_HIST_SESSION关联以获取OS用户：

SELECT
    ash.sample_time,
    u.username,
    s.osuser,
    sql.sql_text
FROM
    DBA_HIST_ACTIVE_SESS_HISTORY ash
JOIN DBA_USERS u ON ash.user_id = u.user_id
JOIN DBA_HIST_SQLTEXT sql ON ash.sql_id = sql.sql_id
-- 关联会话历史视图获取OS用户信息
JOIN DBA_HIST_SESSION s 
    ON ash.dbid = s.dbid
    AND ash.instance_number = s.instance_number
    AND ash.snap_id = s.snap_id
    AND ash.session_id = s.session_id
    AND ash.session_serial# = s.session_serial#
WHERE ash.sample_time BETWEEN TO_DATE('2024-01-01', 'YYYY-MM-DD') AND SYSDATE;

注意事项 #

• 确认DBA_HIST_SESSION存在对应快照数据：AWR默认会捕获会话信息，若快照保留周期过短或捕获频率过低，可能缺失目标时间段数据，可通过DBA_HIST_SNAPSHOT查看快照范围。
• SNAP_ID的匹配可确保获取对应采样时间点的会话信息，避免数据错位。
• 若需实时/近实时数据，直接查询V$SESSION结合V$SQL即可；历史数据场景下，DBA_HIST_SESSION是AWR体系中唯一可靠的OS用户信息来源。

内容的提问来源于stack exchange，提问作者altink