如何查询Oracle数据库指定时间点后发生过变更的所有表

你之前使用的两个SQL方案均针对表结构(DDL)变更或依赖统计信息刷新的延迟增量变更记录，而资产转移操作属于数据(DML)变更，不会触发表的last_ddl_time更新，all_tab_modifications默认也不会实时同步DML变更，因此无法捕获相关记录。

方案1：利用已开启的Oracle审计功能 #

如果你的环境提前开启了DML审计，可直接查询审计视图获取变更记录：

SELECT DISTINCT owner, obj_name table_name, action_name, timestamp
FROM dba_audit_object
WHERE timestamp > SYSDATE - 1 -- 替换为你需要查询的起始时间
  AND owner = 'FA' -- Oracle EBS资产模块默认表所有者为FA，可按实际调整
  AND action_name IN ('INSERT', 'UPDATE', 'DELETE')
ORDER BY timestamp DESC;

方案2：使用LogMiner分析归档/重做日志 #

无需提前配置，只要操作对应的日志未被删除即可使用：

1. 确认资产转移操作时间范围内的归档日志路径，将日志加入LogMiner分析队列：

-- 第一个日志调用
EXEC DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '你的归档日志全路径', OPTIONS => DBMS_LOGMNR.NEW);
-- 多个日志时后续调用
EXEC DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '其他归档日志全路径', OPTIONS => DBMS_LOGMNR.ADDFILE);

2. 启动指定时间范围的日志分析：

EXEC DBMS_LOGMNR.START_LOGMNR(
  STARTTIME => '2024-XX-XX 00:00:00', -- 替换为资产转移操作的开始时间
  ENDTIME => '2024-XX-XX 23:59:59', -- 替换为资产转移操作的结束时间
  OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.COMMITTED_DATA_ONLY
);

3. 查询该时间段内资产模块发生DML变更的所有表：

SELECT DISTINCT seg_owner, seg_name table_name
FROM v$logmnr_contents
WHERE seg_owner = 'FA'
  AND operation IN ('INSERT','UPDATE','DELETE')
  AND seg_type_name = 'TABLE';

4. 分析完成后关闭LogMiner：

EXEC DBMS_LOGMNR.END_LOGMNR();

方案3：EBS资产模块专属查询方案 #

Oracle EBS的资产转移操作有标准的事务记录，可直接通过模块内置表定位变更：

1. 首先查询资产转移的事务头记录：

SELECT * FROM FA.FA_TRANSACTION_HEADERS
WHERE TRANSACTION_DATE > SYSDATE - 1
  AND TRANSACTION_TYPE_CODE = 'TRANSFER';

2. 拿到对应事务的TRANSACTION_HEADER_ID后，可关联查询资产模块所有子表，就能得到所有发生变更的关联表，资产转移通常涉及的核心表包括FA_ADDITIONS、FA_BOOKS、FA_DISTRIBUTION_HISTORY。

方案4：复现操作时临时捕获变更 #

如果可以在测试环境复现资产转移操作，可提前开启表监控实时捕获变更：

1. 开启资产模块所有表的DML监控：

BEGIN
  FOR rec IN (SELECT table_name FROM all_tables WHERE owner = 'FA') LOOP
    EXECUTE IMMEDIATE 'ALTER TABLE FA.'||rec.table_name||' MONITORING';
  END LOOP;
END;
/

2. 执行资产转移操作后，手动刷新监控数据：

EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

3. 查询变更的表：

SELECT table_name, inserts, updates, deletes
FROM all_tab_modifications
WHERE table_owner = 'FA'
  AND timestamp > SYSDATE - 1;

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