Oracle 9i/10g 閃回查詢完整解析：Flashback Query、Version Query、Transaction Query、Table/Database/Drop 多維度時光機

閃回技術演進脈絡

核心偽欄位（Version Query）

• VERSIONS_STARTSCN / VERSIONS_ENDSCN：變更生效/失效的 SCN
• VERSIONS_STARTTIME / VERSIONS_ENDTIME：對應時間戳
• VERSIONS_XID：變更事務 ID
• VERSIONS_OPERATION：Insert / Update / Delete

實戰範例：匯率表變更審計

-- 建表與模擬業務操作
CREATE TABLE rates (currency VARCHAR2(4), rate NUMBER(15,10));
INSERT INTO rates VALUES ('EURO',1.1012); COMMIT;
UPDATE rates SET rate=1.1014; COMMIT;
UPDATE rates SET rate=1.1013; COMMIT;
DELETE FROM rates; COMMIT;
INSERT INTO rates VALUES ('EURO',1.1016); COMMIT;
UPDATE rates SET rate=1.1011; COMMIT;

-- 版本查詢：完整變更歷程
SELECT versions_starttime, versions_endtime, versions_xid,
       versions_operation, rate
FROM rates
VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE
ORDER BY versions_starttime;

輸出將顯示 6 筆記錄，含 Insert、Update、Delete、Re-insert 全過程，VERSIONS_ENDTIME 為 NULL 代表當前版本。

Transaction Query：取得回滾 SQL

SELECT undo_sql
FROM flashback_transaction_query
WHERE xid = '000A000D00000029';  -- 取自 VERSIONS_XID

-- 結果範例
-- INSERT INTO "ANANDA"."RATES"("CURRENCY","RATE") VALUES ('EURO','1.1013');

執行該 UNDO_SQL 即可反向修復該事務的變更。

Flashback Table 表級時光機

-- 前置條件
ALTER TABLE test ENABLE ROW MOVEMENT;

-- 恢復表至 30 分鐘前
FLASHBACK TABLE test TO TIMESTAMP SYSDATE - INTERVAL '30' MINUTE;

-- 或指定 SCN
FLASHBACK TABLE test TO SCN 123456789;

Flashback Drop 回收站

DROP TABLE test_drop;                    -- 進入回收站，重命名為 BIN$...
SHOW RECYCLEBIN;                         -- 查看回收站內容
FLASHBACK TABLE test_drop TO BEFORE DROP; -- 還原
-- 或指定回收站物件名
FLASHBACK TABLE "BIN$b+XkkO1RS5K10uKo9BfmuA==$0" TO BEFORE DROP;

-- 清除回收站
PURGE TABLE test_drop;        -- 單表
PURGE RECYCLEBIN;             -- 當前用戶
PURGE DBA_RECYCLEBIN;         -- 全庫（需 SYSDBA）

-- 永久刪除不進回收站
DROP TABLE test_drop PURGE;

Flashback Database 整庫時光機

-- 啟用（需設定快速恢復區）
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=10G;
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='/u01/fra';
ALTER DATABASE FLASHBACK ON;

-- 閃回整庫
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
FLASHBACK DATABASE TO TIMESTAMP TO_TIMESTAMP('2024-01-15 14:00:00','YYYY-MM-DD HH24:MI:SS');
ALTER DATABASE OPEN RESETLOGS;

前置條件與限制

• 所有閃回特性依賴 自動 UNDO 管理（UNDO_MANAGEMENT=AUTO）與足夠 UNDO_RETENTION。
• FLASHBACK DATABASE 需企業版、啟用 Flashback Logs、資料庫處於 MOUNT 狀態、RESETLOGS 開啟。
• DDL 操作（TRUNCATE、ALTER TABLE DROP COLUMN 等）會中斷版本鏈，無法閃回跨 DDL 的變更。
• 空間壓力下 UNDO/Flashback Logs 可能被覆寫，導致閃回窗口縮短，監控 V$UNDOSTAT、V$FLASHBACK_DATABASE_LOG。

選型建議