怎样在PL/SQL中编写触发器记录用户注销与会话时长-SQL-PHP中文网

oracle不支持logoff触发器，仅支持logon、startup、shutdown等数据库事件；logoff需通过审计视图dba_audit_session结合登录日志表定时补全实现。

怎样在pl/sql中编写触发器记录用户注销与会话时长

触发器无法捕获用户注销事件

PL/SQL 触发器（BEFORE/AFTER LOGON 或 BEFORE/AFTER LOGOFF）在标准 Oracle 数据库中**不支持 LOGOFF 事件**。你写 AFTER LOGOFF ON DATABASE 会直接报错 ORA-00604: error occurred at recursive SQL level 1，因为 Oracle 不允许在用户断开连接时执行 DML 触发逻辑——此时会话上下文已不可靠，事务无法提交，甚至连接可能已释放。

替代方案：用数据库审计 + `V$SESSION` 快照

真实可行的做法是放弃“触发器捕获注销”，转而用定时快照 + 审计日志交叉比对。核心思路是：LOGON 触发器只记录登录时间，注销靠后台作业查 V$SESSION 中状态变为 'INACTIVE' 且 LAST_CALL_ET > 300（例如空闲超 5 分钟），再结合 DBA_AUDIT_SESSION 的 LOGOFF 记录补全。

CREATE OR REPLACE TRIGGER logon_audit_trg AFTER LOGON ON DATABASE —— 只做登录记录，插入到自建表 user_session_log，存 USERNAME、OSUSER、MACHINE、LOGON_TIME、SID、SERIAL#
确保开启标准审计：AUDIT SESSION;（需 audit_sys_operations=FALSE 且用户有 SELECT ANY DICTIONARY）
定期运行作业（如每分钟）查询 DBA_AUDIT_SESSION 中 RETURNCODE = 0 且 ACTION_NAME = 'LOGOFF' 的新记录，关联 user_session_log 补全 LOGOFF_TIME 和计算 DURATION_SEC

`V$SESSION` 空闲会话不能直接当注销

很多人误以为查 V$SESSION.STATUS = 'INACTIVE' 就是用户登出，其实不是。它只表示当前没在执行 SQL，但连接仍存在（比如应用保持长连接、SQL*Plus 没退出）。真正注销的标志是 V$SESSION 中该行消失，或 DBA_AUDIT_SESSION 出现 LOGOFF 审计记录（RETURNCODE = 0 表示正常退出，1017 是密码错，28000 是账户锁定）。

依赖 V$SESSION 做“伪注销”统计会导致会话时长严重高估（比如一个空闲 8 小时的连接被算成 8 小时时长）
DBA_AUDIT_SESSION 的 LOGOFF_TIME 字段是准确的，但它延迟写入——通常在会话真正终止后几秒内才落盘，不能用于实时告警
如果数据库未启用审计（AUDIT_TRAIL = NONE），则 DBA_AUDIT_SESSION 为空，这条路彻底走不通

最小可用实现：登录记录 + 审计轮询脚本

下面是一个可直接部署的组合方案，不需要额外进程，仅靠 Oracle 自带能力：

-- 1. 建表（注意：不要在 SYSTEM 表空间）
CREATE TABLE user_session_log (
  log_id      NUMBER GENERATED BY DEFAULT AS IDENTITY,
  username    VARCHAR2(30),
  osuser      VARCHAR2(100),
  machine     VARCHAR2(100),
  logon_time  DATE,
  sid         NUMBER,
  serial#     NUMBER,
  logoff_time DATE,
  duration_sec NUMBER
);
<p>-- 2. 登录触发器（只写登录）
CREATE OR REPLACE TRIGGER logon_audit_trg 
AFTER LOGON ON DATABASE
BEGIN
INSERT INTO user_session_log (username, osuser, machine, logon_time, sid, serial#)
SELECT ORA_LOGIN_USER, SYS_CONTEXT('USERENV','OS_USER'), 
SYS_CONTEXT('USERENV','HOST'), SYSDATE, SID, SERIAL#
FROM V$SESSION WHERE SID = SYS_CONTEXT('USERENV','SID');
EXCEPTION WHEN OTHERS THEN NULL; -- 防止登录失败
END;</p><p>-- 3. 手动补全注销（作为定期 JOB 运行）
MERGE INTO user_session_log t
USING (
SELECT username, timestamp, returncode 
FROM dba_audit_session 
WHERE action_name = 'LOGOFF' 
AND returncode = 0 
AND timestamp > SYSDATE - 1/24 -- 过去一小时
) s
ON (t.username = s.username AND t.logon_time  24  60 * 60)
WHERE t.logoff_time IS NULL;</p>

这个方案里最易忽略的是：审计记录的 timestamp 是服务器本地时区，而 SYSDATE 也是同一时区，但如果你的客户端时区不同，LOGON_TIME 和 LOGOFF_TIME 在应用层显示时可能错位——得统一转成 SYSTIMESTAMP AT TIME ZONE 'UTC' 存储才可靠。