如何查询指定表的INSERT/UPDATE/DELETE操作及操作用户ID

我可以通过以下查询查看最近一小时被修改的表：

select * from ALL_TAB_MODIFICATIONS where timestamp> sysdate-1/24 

现在想了解某张表上执行的INSERT、UPDATE、DELETE具体操作，同时获取操作对应的用户ID，该怎么实现？

我尝试使用审计功能：
在sqlplus中执行SHOW PARAMETER AUDIT_TRAIL，结果返回DB --OK
接着执行AUDIT ALL BY SEV BY ACCESS，提示audit succeeded
然后查询所有审计视图（执行语句：SELECT view_name FROM dba_views WHERE view_name LIKE 'DBA%AUDIT%';），例如查询select * from dba_audit_exists;，但所有审计表/视图都为空。

一、修复审计配置并获取DML操作记录 #

你的审计配置存在细节问题，导致未生成有效记录，按以下步骤调整：

1. 完善审计参数
   执行SHOW PARAMETER AUDIT_TRAIL，确保参数值为DB, EXTENDED（仅DB模式不会记录完整SQL语句）。若不符合，修改参数并重启数据库：

   ALTER SYSTEM SET AUDIT_TRAIL=DB,EXTENDED SCOPE=SPFILE;
   

2. 针对目标表配置审计
   你之前的命令是审计用户SEV的所有操作，若要追踪特定表的DML，需直接针对表配置：

   -- 替换YOUR_TABLE为实际表名
   AUDIT INSERT, UPDATE, DELETE ON YOUR_TABLE BY ACCESS;
   

   该命令会审计所有用户对目标表的DML操作，且每一次操作都会生成一条审计记录。

3. 查询正确的审计视图
   DML操作的审计记录存储在DBA_AUDIT_TRAIL或DBA_COMMON_AUDIT_TRAIL中（DBA_AUDIT_EXISTS仅记录对象存在性检查的审计），查询语句示例：

   SELECT username, os_username, timestamp, action_name, sql_text
   FROM DBA_COMMON_AUDIT_TRAIL
   WHERE obj_name = 'YOUR_TABLE' -- 注意使用大写表名
     AND action_name IN ('INSERT', 'UPDATE', 'DELETE')
     AND timestamp > SYSDATE - 1/24; -- 筛选最近1小时的记录
   

二、其他可选追踪方案 #

如果审计功能无法快速生效，还可以使用以下方法：

• 闪回版本查询（需数据库开启闪回功能）
  直接查询表的历史修改记录，包含操作类型和执行用户：

  SELECT versions_starttime, versions_endtime, versions_operation, versions_username, t.*
  FROM YOUR_TABLE VERSIONS BETWEEN TIMESTAMP SYSDATE-1/24 AND SYSDATE t
  ORDER BY versions_starttime DESC;
  

  其中versions_operation列：I代表INSERT，U代表UPDATE，D代表DELETE。

• 自定义触发器日志
  通过触发器将DML操作记录到自定义日志表：

  1. 创建日志表：

     CREATE TABLE dml_audit_log (
       log_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
       table_name VARCHAR2(128),
       operation VARCHAR2(10),
       username VARCHAR2(30),
       operation_time TIMESTAMP,
       sql_text CLOB
     );
     

  2. 创建目标表的触发器：

     CREATE OR REPLACE TRIGGER trg_your_table_dml
     AFTER INSERT OR UPDATE OR DELETE ON YOUR_TABLE
     FOR EACH ROW
     DECLARE
       v_sql CLOB;
     BEGIN
       SELECT sql_fulltext INTO v_sql FROM v$sql WHERE sql_id = (SELECT sql_id FROM v$session WHERE audsid = USERENV('SESSIONID'));
       INSERT INTO dml_audit_log (table_name, operation, username, operation_time, sql_text)
       VALUES ('YOUR_TABLE',
               CASE WHEN INSERTING THEN 'INSERT' WHEN UPDATING THEN 'UPDATE' WHEN DELETING THEN 'DELETE' END,
               USER,
               SYSTIMESTAMP,
               v_sql);
     END;
     /
     

  之后查询dml_audit_log即可获取操作记录。

内容的提问来源于stack exchange，提问作者Pierre-olivier Gendraud