深入掌握 Oracle 資料一致性與回滾機制：從 UNDO 到 Flashback Query

核心概念：讀取一致性與 UNDO

Oracle 透過 UNDO 記錄 實現「讀取一致性」：查詢只能看到提交過的資料，或特定時間點（SCN）的資料快照。未提交的變更對其他 Session 不可見，查詢會自動從 UNDO 段重建舊版本資料塊。

實驗 1：多 Session 驗證讀取一致性

-- Session 1
CREATE TABLE c (a INT, b NUMBER);
INSERT INTO c VALUES (1,2);
INSERT INTO c VALUES (3,4);
COMMIT;

-- Session 2
SELECT * FROM c;  -- 看到 (1,2) (3,4)

-- Session 1 修改但未提交
UPDATE c SET b=10 WHERE a=1;

-- Session 2 再查
SELECT * FROM c;  -- 仍看到 (1,2) (3,4)  ← 從 UNDO 重建

-- Session 1 提交
COMMIT;

-- Session 2 再查
SELECT * FROM c;  -- 看到 (1,10) (3,4)  ← 正式資料

結論： 未提交變更隔離於 UNDO，確保多使用者環境下的資料一致性。

實驗 2：ROLLBACK 事務回滾

-- 繼續上例，Session 1
UPDATE c SET b=2 WHERE a=1;  -- b 變 2
ROLLBACK;                    -- 回滾
SELECT * FROM c;             -- b 恢復為 10（上一次提交值）

ROLLBACK 利用 UNDO 將資料還原至最近一次 COMMIT 狀態，僅限「未提交」事務。

進階：已提交資料的時間點恢復

若資料已提交甚至被刪除，可用 Flashback Query (AS OF SCN/TIMESTAMP) 找回歷史版本：

-- 1. 記錄當前 SCN
SELECT current_scn FROM v$database;  -- 假設 693636

-- 2. 刪除並提交
DELETE FROM c; COMMIT;

-- 3. 查詢歷史快照
SELECT * FROM c AS OF SCN 693636;  -- 找回被刪資料

-- 4. 恢復資料
INSERT INTO c SELECT * FROM c AS OF SCN 693636;
COMMIT;

同理，將 SCN 換算為 TIMESTAMP 即可「按時間點恢復」。企業級環境建議搭配 UNDO_RETENTION 保證足夠回溯窗口，並啟用 FLASHBACK DATABASE 實現更大範圍、更快速的還原。

運維建議

• 監控 V$UNDOSTAT 確保 UNDO 表空間與保留時間滿足業務回溯需求。
• 長時間查詢遇到 ORA-01555 Snapshot too old，優先調大 UNDO_RETENTION 或擴充 UNDO 表空間。
• 重要資料表可建立 FLASHBACK ARCHIVE（Total Recall）實現長期歷史追蹤。