如何查询指定日期与时间的Oracle数据库所有连接
Oracle 历史指定时间点全量连接查询实现指引
为什么V$SESSION无法满足历史查询需求
V$SESSION是Oracle实例内存中维护的动态性能视图,仅存储当前时刻的所有会话连接信息,实例重启、会话断开后对应内存数据就会被释放,没有任何持久化存储能力,完全无法回溯历史时间点的连接状态。满足持久化存储要求的连接相关数据源主要有以下几类,全部为落盘存储的表或视图,不会随实例重启丢失,具备历史数据回溯能力:
核心可用的持久化连接存储资源
- 数据库审计存储表
这是Oracle原生最接近“全量连接事务存储”的资源,只要启用了数据库连接审计,所有的连接、断开动作都会被实时落盘记录。- 传统审计模式下,所有连接相关记录存储在底层表
SYS.AUD$中,记录内容包括每次连接的登录时间、登出时间、登录用户名、客户端主机名、客户端IP、连接认证方式。 - 统一审计模式下,连接记录存储在
UNIFIED_AUDIT_TRAIL对应的底层持久化表中,字段逻辑和传统审计基本一致。
这类表的核心优势是可以100%还原任意时间点的全量连接(包含空闲连接),不需要依赖定时采样,只要根据登录/登出时间做匹配即可。参考查询逻辑(还原2024-05-20 14:30时刻的所有有效连接):
SELECT DISTINCT userid, userhost, terminal, timestamp# AS login_time, logoff_time FROM sys.aud$ WHERE action# = 100 -- 100为Oracle原生定义的数据库连接动作编码 -- 匹配登录时间早于目标时间点 AND timestamp# <= DATE '2024-05-20' + INTERVAL '14:30' HOUR TO MINUTE -- 匹配会话没有在目标时间点之前断开 AND (logoff_time IS NULL OR logoff_time > DATE '2024-05-20' + INTERVAL '14:30' HOUR TO MINUTE); - 传统审计模式下,所有连接相关记录存储在底层表
- AWR自动快照存储视图
Oracle默认开启的自动工作负载仓库,会定时(默认1小时1次,可调整采样频率)对数据库的运行状态做快照落盘,快照数据默认保留8天,可调整为更长时间。- 定位目标时间所属快照:可通过
DBA_HIST_SNAPSHOT视图查询所有快照的时间区间,找到覆盖你目标查询时间的快照ID,参考查询:SELECT snap_id, begin_interval_time, end_interval_time FROM DBA_HIST_SNAPSHOT ORDER BY begin_interval_time DESC; - 对应快照的会话数据存储在
DBA_HIST_ACTIVE_SESS_HISTORY视图中,可查到快照时间点附近的活跃会话明细(含连接用户、主机、服务名、连接状态)。
注意边界:AWR默认仅采样处于活跃状态的会话,空闲连接不会被记录到快照中,因此用AWR查询的连接结果不包含全量空闲连接,存在数据缺失。
- 定位目标时间所属快照:可通过
- 自建连接采集持久化表
这是生产环境最常用的灵活方案,因为AWR、审计的默认能力都有边界,很多团队会自建一张普通数据库表(比如命名为db_conn_history_log),通过定时任务(DBMS_JOB/DBMS_SCHEDULER)每隔固定时间(如5分钟、15分钟),将当时V$SESSION查询到的全量连接明细插入到这张表中持久化存储。
这类表的优势是完全可控:可以自定义采集间隔、自定义存储的连接字段、自定义数据保留周期,任意采集时间点的连接明细都可以直接查询,不存在数据缺失的问题。
常见实现注意事项
- 所有V$开头的动态性能视图都是内存态,没有持久化能力,不要尝试用这类视图回溯历史数据。
- AWR的数据保留周期可通过
DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS包调整,最长可设置为保留几年的快照数据。 - 连接审计如果全量开启,
AUD$表的增长速度会很快,需要定期对历史审计数据做归档清理,避免占满系统表空间。 - RAC架构下,以上所有的表、视图都是全局分布式存储,自动覆盖所有节点的连接数据,不需要逐实例查询。
内容的提问来源于stack exchange,提问作者not_a_comp_scientist
相关产品推荐
相关产品推荐

