Oracle中如何批量查询Schema内表的最后DML操作信息？

先拆解你遇到的两种查询方法的问题 #

1. 基于DBA_TAB_MODIFICATIONS的Query1问题 #

你只查到少量表数据，核心原因有这几个：

• 这个视图的数据依赖Oracle的监控统计信息刷新，默认不是实时同步的。如果表执行DML后没触发统计信息更新，视图里就不会有记录。可以先手动执行EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;刷新监控数据，再查就能看到更全结果。
• 你的查询加了当天日期过滤TO_CHAR(TIMESTAMP,'DD.MM.YYYY') = TO_CHAR(sysdate,'DD.MM.YYYY')，会直接排除当天没有DML操作的表；如果要覆盖整个Schema的所有表，去掉这个条件即可。
• 不用手动指定表名列表，保留table_owner='SCHEMA_NAME'就能自动包含该Schema下所有表。

修正后的Query1示例：

SELECT 
  TABLE_OWNER,
  TABLE_NAME,
  INSERTS,
  UPDATES,
  DELETES,
  TIMESTAMP AS LAST_CHANGE 
FROM DBA_TAB_MODIFICATIONS 
WHERE table_owner='SCHEMA_NAME'
ORDER BY LAST_CHANGE DESC;

2. 基于ora_rowscn的Query2问题 #

你碰到的ORA-08181错误，是因为SCN_TO_TIMESTAMP只能转换近期的SCN——Oracle会定期清理过旧的SCN与时间戳的映射关系，如果表的最后DML操作时间太久远，就会触发这个报错。另外，查询慢是因为要扫描全表找最大ora_rowscn，大表的开销会非常大。

如果要用这种方法查询多个表，需要用UNION ALL拼接每个表的查询，示例：

SELECT 'TABLE1' AS TABLE_NAME, MAX(ora_rowscn) AS MAX_SCN, 
       CASE WHEN MAX(ora_rowscn) IS NOT NULL THEN SCN_TO_TIMESTAMP(MAX(ora_rowscn)) END AS LAST_DML_TIME
FROM SCHEMA_NAME.TABLE1
UNION ALL
SELECT 'TABLE2' AS TABLE_NAME, MAX(ora_rowscn) AS MAX_SCN, 
       CASE WHEN MAX(ora_rowscn) IS NOT NULL THEN SCN_TO_TIMESTAMP(MAX(ora_rowscn)) END AS LAST_DML_TIME
FROM SCHEMA_NAME.TABLE2
-- 继续添加其他表...

不过这种方式对大量表来说太繁琐，且仍存在SCN过期的风险，不适合批量查询。

你的疑问逐个解答 #

1. 如何一次性查询所有表？ #

最便捷的是用修正后的DBA_TAB_MODIFICATIONS方法；如果一定要用ora_rowscn，可以用PL/SQL动态生成批量查询脚本：

SET SERVEROUTPUT ON;
DECLARE
  v_sql VARCHAR2(4000);
BEGIN
  FOR rec IN (SELECT table_name FROM dba_tables WHERE owner='SCHEMA_NAME') LOOP
    v_sql := v_sql || 'SELECT ''' || rec.table_name || ''' AS TABLE_NAME, MAX(ora_rowscn) AS MAX_SCN, ' ||
             'CASE WHEN MAX(ora_rowscn) IS NOT NULL THEN SCN_TO_TIMESTAMP(MAX(ora_rowscn)) END AS LAST_DML_TIME ' ||
             'FROM SCHEMA_NAME.' || rec.table_name || ' UNION ALL ';
  END LOOP;
  -- 去掉最后多余的UNION ALL
  v_sql := RTRIM(v_sql, 'UNION ALL ');
  DBMS_OUTPUT.PUT_LINE(v_sql);
END;
/

执行这段代码会生成所有表的查询语句，复制出来执行即可，还是要注意SCN过期的问题。

2. 哪种查询方法正确？ #

两种方法各有适用场景：

• DBA_TAB_MODIFICATIONS：适合批量查询，效率高，刷新监控信息后数据足够准确，是日常批量查询的首选。
• ora_rowscn：适合单个小表的精确查询，能拿到最准确的最后DML时间，但批量操作繁琐、效率低，还有SCN过期风险，不推荐批量使用。

3. 是否有更优方案？ #

如果需要长期、实时监控表的DML操作，推荐两种方案：

• 启用Oracle审计：配置表级DML审计，能记录所有DML操作的时间、执行用户等完整信息，不过需要额外的存储空间和配置成本。
• 创建DML触发器：为目标表创建触发器，将最后DML时间写入专门的监控表（比如TABLE_DML_LOG），可以实时获取最新DML时间，对性能有轻微影响，适合关键表的监控。

关于MINUS查询的时间间隔问题 #

你写的这个查询是通过对比表在两个时间点的数据差异，判断是否有DML操作。时间间隔的设置取决于你要检查的时间段：

• 如果想检查过去24小时内的变化，用systimestamp - interval '1' day是合理的。
• 如果要检查更短的周期，比如过去1小时，改成systimestamp - interval '1' hour即可。
  不过这个方法只能判断表是否有变化，无法直接拿到最后DML时间，且大表执行起来极慢，不适合批量查询。

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