Oracle更新提交后如何查看列的历史值及指定日期值

一、查询列更新前的数值 #

如果Oracle数据库开启了闪回功能（默认通常开启），可以通过闪回版本查询获取该记录的版本变更历史，找到2022年1月1日更新操作前的salary值：

SELECT salary, VERSIONS_STARTTIME, VERSIONS_ENDTIME, VERSIONS_OPERATION
FROM emp VERSIONS BETWEEN TIMESTAMP MINVALUE AND TIMESTAMP TO_TIMESTAMP('2022-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
WHERE id = 1111
ORDER BY VERSIONS_STARTTIME DESC;

• VERSIONS_OPERATION列会显示操作类型：U代表更新，I代表插入，D代表删除
• 结果中最新的一条（除更新后版本外）就是更新前的salary值

也可以通过闪回事务查询定位具体变更，提取更新前的数值：

SELECT undo_sql
FROM flashback_transaction_query
WHERE table_name = 'EMP' AND row_id = (SELECT rowid FROM emp WHERE id = 1111)
AND commit_time BETWEEN TO_TIMESTAMP('2022-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') AND SYSTIMESTAMP;

执行后返回的回退SQL中，会包含更新前的salary数值。

二、查询2021年12月31日的列值 #

使用Oracle的AS OF TIMESTAMP闪回查询，直接获取指定时间点的记录状态：

SELECT salary
FROM emp AS OF TIMESTAMP TO_TIMESTAMP('2021-12-31 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
WHERE id = 1111;

注意：该查询生效的前提是数据库undo_retention参数设置的保留时间覆盖了2021年12月31日至当前的时间段，或开启了闪回日志。若undo数据已被覆盖，则无法查询到该时间点的数值。

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