Oracle Flashback Query 详解

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

---

1. 概述

Flashback Query 查询历史数据[1]：

详细见：Oracle Flashback 技术全集。

---

2. 原理

- 基于 Undo 数据
- 查询过去时间点数据
- 不修改数据

---

3. AS OF TIMESTAMP

3.1 基本

SELECT * FROM employees 
AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' HOUR);

SELECT * FROM employees 
AS OF TIMESTAMP TO_TIMESTAMP('2026-07-21 10:00:00', 'YYYY-MM-DD HH24:MI:SS');

3.2 比较

-- 当前 vs 1 小时前
SELECT e.id, e.name AS new_name, e_old.name AS old_name
FROM employees e,
  employees AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' HOUR) e_old
WHERE e.id = e_old.id(+)
  AND (e.name != e_old.name OR e_old.name IS NULL);

---

4. AS OF SCN

SELECT * FROM employees AS OF SCN 1234567;

-- 当前 SCN
SELECT current_scn FROM v$database;

---

5. DBMS_FLASHBACK

5.1 启用

EXEC DBMS_FLASHBACK.ENABLE_AT_TIME(SYSTIMESTAMP - INTERVAL '1' HOUR);
-- 之后所有查询都是历史

5.2 禁用

EXEC DBMS_FLASHBACK.DISABLE;

5.3 SCN

EXEC DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER(1234567);

---

6. 限制

6.1 DDL

- 不能 DDL
- 不能 DML（除非 ENABLE 后 DISABLE）

6.2 Undo 保留

- 取决于 Undo 保留时间
- undo_retention
- 表空间大小

6.3 视图

- 不能在视图定义中使用
- 子查询可以

---

7. 应用场景

7.1 误操作恢复

-- 找回误删除数据
INSERT INTO employees 
SELECT * FROM employees 
AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '30' MINUTE)
WHERE id = 100;

7.2 数据对比

SELECT * FROM employees
MINUS
SELECT * FROM employees AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' HOUR);

7.3 审计

SELECT id, salary, 
  (SELECT salary FROM employees AS OF TIMESTAMP (SYSTIMESTAMP - 1) 
   WHERE id = e.id) AS old_salary
FROM employees e;

7.4 趋势分析

SELECT name, salary FROM employees AS OF TIMESTAMP ... 
UNION ALL
SELECT name, salary FROM employees AS OF TIMESTAMP ...;

---

8. Undo 管理

8.1 保留

ALTER SYSTEM SET undo_retention = 3600;  -- 秒

8.2 表空间

ALTER DATABASE DATAFILE '/u01/undotbs01.dbf' RESIZE 10G;
ALTER TABLESPACE undo_tb1 RETENTION GUARANTEE;

8.3 监控

SELECT tablespace_name, retention 
FROM dba_tablespaces WHERE contents = 'UNDO';

SELECT * FROM v$undostat;

---

9. 性能

9.1 开销

- Undo 查询
- 索引利用
- 一般可接受

9.2 时间限制

- Undo 保留时间
- 太久可能失败
- ORA-01555

---

10. 常见坑与排错

10.1 ORA-01555

- 快照太旧
- Undo 不足
- 调整 undo_retention

10.2 ORA-08180

- 时间超出 Undo 范围
- 检查保留时间

10.3 ORA-01466

- DDL 后无法查询
- 检查

---

11. 最佳实践

1. Undo 充足：保留时间
2. RETENTION GUARANTEE：保证
3. 测试：场景
4. 审计：变更
5. 对比：历史
6. 恢复：误操作
7. 监控：Undo 使用
8. 文档：操作
9. 备份：兜底
10. 演练：定期

---

12. 参考资料

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