Oracle 閃回技術完整指南：9i 查詢到 10g 表/資料庫/刪除/版本/事務多維度恢復

背景

人為錯誤佔系統故障 40%，傳統備份/恢復耗時且服務中斷。9i 引入閃回查詢，10g 大幅擴充為多維度閃回家族。

Oracle 9i 閃回查詢

原理

• 依賴自動 UNDO 管理 (AUM)，UNDO_MANAGEMENT=AUTO, UNDO_RETENTION=n 決定可閃回時間上限
• 利用 UNDO 區段儲存修改前影像，DBMS_FLASHBACK 套件實現
• 可用系統時間或 SCN 指定只讀視圖，恢復誤刪/誤改/誤增資料
• 無法恢復 DDL (DROP)

應用場景

• 自我修復：重要記錄誤刪，時光倒流查回重插
• 郵件/語音郵件恢復
• 帳務歷史查詢：如匯率歷史
• 決策支援：長交易分析歷史快照

操作範例

-- 建立用戶授權
CREATE USER flashtest IDENTIFIED BY flashtest;
GRANT CONNECT, RESOURCE TO flashtest;
GRANT EXECUTE ON dbms_flashback TO flashtest;

-- 測試表
CREATE TABLE test(id NUMBER(3));
INSERT INTO test VALUES(1); INSERT INTO test VALUES(2); COMMIT;
CREATE TABLE rec_date(date_scn);

-- 等待 5 分鐘確保 SCN 記錄
EXEC dbms_flashback.disable;
INSERT INTO rec_date SELECT sysdate FROM dual; COMMIT;
DELETE FROM test WHERE id=1; COMMIT;

-- 閃回查詢
DECLARE restore_scn DATE;
BEGIN SELECT date_scn INTO restore_scn FROM rec_date;
DBMS_FLASHBACK.ENABLE_AT_TIME(restore_scn); END;
SELECT * FROM test; -- 可見 id=1,2

注意：Oracle 每 5 分鐘記錄 SCN，時間型閃回會轉換為最近 SCN。需精確恢復請用 DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER 取得 SCN 為基準。

Oracle 10g 閃回家族

1. FLASHBACK DATABASE

SQL 語句讓整庫時光倒流至指定時間/SCN，無需時間點恢復。需設定 DB_RECOVER_FILE_DEST、DB_RECOVER_FILE_DEST_SIZE，執行 ALTER DATABASE FLASHBACK ON。
FLASHBACK DATABASE TO TIMESTAMP ... / TO SCN ...

2. FLASHBACK TABLE

將表（含附屬物件）恢復至舊時間點，改變當前表狀態（不同於 Query 僅查詢）。需 FLASHBACK ANY TABLE 權限、SELECT/INSERT/DELETE/ALTER 權限、啟用 ROW MOVEMENT。
FLASHBACK TABLE t TO TIMESTAMP ...

3. FLASHBACK DROP (回收站)

DROP TABLE 不立即釋放空間，自動重命名放入回收站（虛擬容器）。
SHOW RECYCLEBIN 查看 → FLASHBACK TABLE t TO BEFORE DROP 或 FLASHBACK TABLE "BIN$..." TO BEFORE DROP 恢復。
管理：PURGE TABLE t、PURGE RECYCLEBIN、PURGE USER/DBA_RECYCLEBIN。
確認刪除不進回收站：DROP TABLE t PURGE。

4. FLASHBACK VERSION QUERY

電影倒敘般查看表在時段內所有變更。依賴 AUM，VERSIONS BETWEEN 語法。
偽欄位：VERSIONS_STARTTIME/ENDTIME, VERSIONS_XID, VERSIONS_OPERATION, VERSIONS_STARTSCN/ENDSCN。
範例：SELECT ... FROM test VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE ORDER BY VERSIONS_STARTTIME

5. FLASHBACK TRANSACTION QUERY

事務級審計與回滾。從 FLASHBACK_TRANSACTION_QUERY 視圖取得 UNDO_SQL（回滾對應 SQL）。
範例：刪除記錄 → 取得 XID → 查詢視圖獲得 INSERT ... UNDO_SQL → 執行即回滾該事務。
優勢：錯誤事務夾雜正確事務時，可精準回滾單一錯誤事務。

結語

閃回技術大幅簡化偶發錯誤恢復，強化可用性與讀一致性。