Oracle 閃回技術完整解析與實戰操作

Oracle Flashback（閃回）技術能讓您在不還原備份的前提下，快速查詢或恢復歷史資料。本文系統整理閃回的核心設定、套件用法、SCN 與時間戳對應關係，並附完整實戰演示。

一、前置環境設定

1. Undo 保留時間

必須設定足夠的 UNDO_RETENTION（秒），確保需要閃回的歷史資料未被覆寫：

ALTER SYSTEM SET UNDO_RETENTION=3600; -- 保留 1 小時

閃回視圖的可用範圍受限於 undo retention interval。

2. DBMS_FLASHBACK 套件核心程序

注意事項：

• ENABLE 程序不可在有作用中交易時執行。
• 不能用 SYS 身分執行此套件。
• 使用 ENABLE_AT_TIME 前，須設定 NLS_DATE_FORMAT 精確到秒（預設僅到天）。

二、Timestamp 與 SCN 的對應機制

Oracle 內部以 SCN 運作，AS OF TIMESTAMP 會自動轉換為 SCN。對應關係儲存在 SYS.SMON_SCN_TIME 表：

• 每 5 分鐘同步一次記錄。
• 最多保留 1440 筆（約 5 天：12×24×5=1440）。
• 若資料庫中斷重啟，實際可閃回範圍可能不足 5 天。

驗證查詢：

SELECT scn, TO_CHAR(time_dp, 'yyyy-mm-dd hh24:mi:ss') FROM sys.smon_scn_time;

10g 新增轉換函數：SCN_TO_TIMESTAMP()、TIMESTAMP_TO_SCN()。

三、實戰演示：誤刪資料恢復

-- 1. 登入資料庫
SQL> CONNECT tivan/tivan

-- 2. 確認資料筆數
SQL> SELECT COUNT(*) FROM t1;  -- 8302 筆

-- 3. 誤刪並提交
SQL> DELETE FROM t1; COMMIT;

-- 4. 取得當前 SCN（或用 TIMESTAMP_TO_SCN 指定時間點）
SQL> SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL; -- 1482649
SQL> SELECT COUNT(*) FROM t1 AS OF SCN 1482600; -- 8302 筆，資料尚在

-- 5. 方式 A：INSERT 回存
SQL> INSERT INTO t1 SELECT * FROM t1 AS OF SCN 1482600; COMMIT;

-- 5. 方式 B：FLASHBACK TABLE 指令（10g+）
SQL> FLASHBACK TABLE t1 TO SCN 1482600; COMMIT;

-- 6. 若是 DROP TABLE，可用：
SQL> FLASHBACK TABLE t1 TO BEFORE DROP; COMMIT;

-- 7. 由時間點換算 SCN
SELECT TIMESTAMP_TO_SCN(TO_TIMESTAMP('2010-10-19 21:00:00','YYYY-MM-DD HH:MI:SS')) FROM DUAL;

四、關鍵限制與建議

• 閃回查詢不修改資料，僅供讀取；恢復需搭配 DML 或 FLASHBACK TABLE。
• 大量資料恢復時，FLASHBACK TABLE 效能優於 INSERT 回存。
• 生產環境建議配合 UNDO_TABLESPACE 容量規劃，確保保留窗口滿足業務 RPO 需求。