Oracle 9i 閃回查詢 (Flashback Query) 實戰：誤刪資料秒級恢復全流程

閃回查詢核心原理

• 依賴 自動回滾段管理 (AUM)，需設定 UNDO_MANAGEMENT=AUTO、UNDO_RETENTION (秒)。
• 利用 Undo 區塊中的「前影像」重建指定時間點/SCN 的一致性視圖。
• 僅支援 DML (INSERT/UPDATE/DELETE) 誤操作恢復，不支援 DDL (DROP/TRUNCATE)。

應用場景

1. 重要記錄誤刪 → 閃回查詢找回 → 重新插入。
2. 誤刪 Email/語音郵件 → 時間點恢復。
3. 歷史匯率/帳務餘額查詢 → 替代歷史表。
4. 決策支援系統對歷史數據建模分析 → 長查詢一致性視圖。

操作實戰範例

-- 1. 建立測試用戶與權限
CREATE USER flashtest IDENTIFIED BY flashtest;
GRANT CONNECT, RESOURCE TO flashtest;
GRANT EXECUTE ON dbms_flashback TO flashtest;
CONNECT flashtest/flashtest;

-- 2. 建表測試資料
CREATE TABLE test (id NUMBER(3));
INSERT INTO test VALUES (1);
INSERT INTO test VALUES (2);
COMMIT;
CREATE TABLE rec_time (ts TIMESTAMP);

-- 3. 記錄基準時間 (等待 5 分鐘確保 SCN 生成)
EXEC dbms_flashback.disable;
INSERT INTO rec_time SELECT SYSTIMESTAMP FROM DUAL;
COMMIT;

-- 4. 模擬誤刪
DELETE FROM test WHERE id=1;
COMMIT;

-- 5. 閃回查詢恢復
DECLARE
  v_ts TIMESTAMP;
BEGIN
  SELECT ts INTO v_ts FROM rec_time;
  DBMS_FLASHBACK.ENABLE_AT_TIME(v_ts);
END;
/
SELECT * FROM test;  -- 顯示 id=1,2 兩筆資料

-- 6. 將資料寫回當前表
INSERT INTO test SELECT * FROM test;  -- 在閃回會話中查詢並寫入
COMMIT;
DBMS_FLASHBACK.DISABLE;

關鍵參數與注意事項

• 基於時間點閃回實際轉換為最近 SCN，若需精確到秒請使用 DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER 取得 SCN 再 ENABLE_AT_SYSTEM_CHANGE_NUMBER。
• 閃回查詢會話為唯讀，不可執行 DML；需在閃回會話查詢出資料後，關閉閃回再寫回當前表。