Oracle Flashback Versions Query 详解

适用版本：Oracle Database 10g / 11g / 12c / 19c / 23ai 文档版本：v1.0 / 2026-07

---

1. 概述

Flashback Versions Query 查询行历史版本[1]：

详细见：Oracle Flashback 技术全集。

---

2. 语法

SELECT versions_xid, versions_starttime, versions_endtime,
       versions_startscn, versions_endscn,
       versions_operation,
       id, name, salary
FROM employees
VERSIONS BETWEEN TIMESTAMP 
  TO_TIMESTAMP('2026-07-21 09:00:00', 'YYYY-MM-DD HH24:MI:SS')
  AND TO_TIMESTAMP('2026-07-21 11:00:00', 'YYYY-MM-DD HH24:MI:SS')
WHERE id = 100;

---

3. 伪列

3.1 versions_xid

- 事务 ID
- 16 进制

3.2 versions_starttime / versions_endscn

- 版本开始/结束 SCN

3.3 versions_starttime / versions_endtime

- 版本开始/结束时间

3.4 versions_operation

- I：Insert
- U：Update
- D：Delete

---

4. SCN 范围

SELECT * FROM employees
VERSIONS BETWEEN SCN 1234567 AND 1234999
WHERE id = 100;

-- 最小到最大
SELECT * FROM employees
VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE
WHERE id = 100;

---

5. 时间范围

SELECT * FROM employees
VERSIONS BETWEEN TIMESTAMP 
  TO_TIMESTAMP('2026-07-21 09:00:00', 'YYYY-MM-DD HH24:MI:SS')
  AND SYSTIMESTAMP
WHERE id = 100;

---

6. Flashback Transaction Query

6.1 关联

SELECT xid, operation, undo_sql
FROM flashback_transaction_query
WHERE xid = HEXTORAW('0A001200AB1234');

6.2 完整恢复

-- 找出事务
SELECT versions_xid, versions_operation, id, salary
FROM employees
VERSIONS BETWEEN TIMESTAMP ... AND ...
WHERE id = 100;

-- 获取 UNDO SQL
SELECT undo_sql 
FROM flashback_transaction_query 
WHERE xid = HEXTORAW('&versions_xid');

-- 执行反向
UPDATE employees SET salary = 5000 WHERE id = 100;

---

7. 限制

7.1 Undo

- 取决于 Undo 保留
- 太久失败

7.2 DDL

- DDL 后版本不可见
- 表结构变更

7.3 对象

- 表
- 不支持视图
- 不支持远程表

---

8. 应用场景

8.1 审计

-- 谁改了数据
SELECT versions_xid, versions_starttime, versions_operation, 
       id, salary
FROM employees
VERSIONS BETWEEN TIMESTAMP ... AND ...
WHERE id = 100
ORDER BY versions_starttime;

8.2 误操作恢复

-- 找回数据
SELECT versions_xid, versions_operation, id
FROM employees
VERSIONS BETWEEN TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' HOUR) AND SYSTIMESTAMP
WHERE id = 100;

-- 反向操作
SELECT undo_sql FROM flashback_transaction_query WHERE xid = ...;

8.3 数据追踪

-- 数据变更历史
SELECT versions_starttime, salary
FROM employees
VERSIONS BETWEEN TIMESTAMP ... AND ...
WHERE id = 100
ORDER BY versions_starttime;

8.4 趋势分析

SELECT versions_starttime, salary
FROM employees
VERSIONS BETWEEN TIMESTAMP ... AND ...
WHERE id = 100;

---

9. 性能

9.1 开销

- Undo 查询
- 索引利用
- 时间范围越小越快

9.2 调优

- 缩小时间范围
- 索引
- Undo 保留

---

10. 常见坑与排错

10.1 ORA-01555

- 快照太旧
- Undo 不足

10.2 ORA-01466

- DDL 后无法查询

10.3 空结果

- 时间范围错
- Undo 不足
- 检查

---

11. 最佳实践

1. Undo 充足：保留时间
2. 时间范围：精确
3. 索引：利用
4. 审计：变更追踪
5. 恢复：UNDO SQL
6. 测试：场景
7. 监控：Undo 使用
8. 文档：操作
9. 备份：兜底
10. 演练：定期

---

12. 参考资料

[1] Oracle Database SQL Language Reference 19c, “Flashback Versions Query” https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/