Oracle 閃回技術深度解析：從基礎設定到 SCN 時間轉換實戰

一、閃回運作的核心前提：Undo 保留時間

閃回查詢能否成功，關鍵在於 Undo 保留時間（UNDO_RETENTION） 是否足夠覆蓋目標時間點。設定方式：

ALTER SYSTEM SET UNDO_RETENTION = <秒數> SCOPE=BOTH;

建議依業務需求設定（如 24 小時 = 86400 秒），並確保 Undo 表空間有足夠空間避免「快照太舊」錯誤（ORA-01555）。

二、DBMS_FLASHBACK 套件：會話層級的時間旅行

Oracle 提供 DBMS_FLASHBACK 套件，讓單一會話在不影響其他用戶的情況下回溯歷史資料：

• ENABLE_AT_TIME(ts)：將會話 SCN 定格在指定時間戳最近的 SCN。
• ENABLE_AT_SYSTEM_CHANGE_NUMBER(scn)：直接指定 SCN。
• GET_SYSTEM_CHANGE_NUMBER：取得當前 SCN。
• DISABLE()：結束閃回，回到當前時間。

注意事項：

1. 不可在作用中交易期間呼叫 ENABLE 程序。
2. 不可用 SYS 身分執行此套件。
3. 執行前務必設定 NLS_DATE_FORMAT 含時分秒（預設僅到天），否則時間精度會遺失。

三、SCN 與 Timestamp 的映射關係：SMON_SCN_TIME 表

Oracle 內部以 SCN 為時間基準，即使使用 AS OF TIMESTAMP 也會轉換為 SCN。映射資料儲存在 SYS.SMON_SCN_TIME 表：

• 每 5 分鐘記錄一筆 SCN-Time 配對。
• 最多保留 1,440 筆（約 5 天），超過則循環覆寫。
• 10g 起提供 SCN_TO_TIMESTAMP()、TIMESTAMP_TO_SCN() 函數直接轉換。

-- 查看近期映射
SELECT scn, TO_CHAR(time_dp, 'yyyy-mm-dd hh24:mi:ss') FROM sys.smon_scn_time;

-- 時間轉 SCN 範例
SELECT timestamp_to_scn(TO_TIMESTAMP('2010-10-19 21:00:00','YYYY-MM-DD HH24:MI:SS')) FROM dual;

四、實戰演練：誤刪資料的閃回恢復

1. 確認誤刪前的 SCN（或用時間轉換）。
2. 驗證：SELECT COUNT(*) FROM t1 AS OF SCN 1482600;
3. 恢復方式二選一：
   • 資料層恢復： INSERT INTO t1 SELECT * FROM t1 AS OF SCN 1482600;
   • 物件層恢復（10g+）： FLASHBACK TABLE t1 TO SCN 1482600;
4. 若為 DROP TABLE：FLASHBACK TABLE t1 TO BEFORE DROP;（需啟用回收筒）。

五、進階應用：閃回交易查詢與審計

結合 VERSIONS BETWEEN SCN 或 VERSIONS BETWEEN TIMESTAMP 可追蹤列級變更歷程，搭配 DBMS_FLASHBACK.TRANSACTION_BACKOUT 甚至能針對性回滾特定交易，是資料保護與合規審計的利器。