Oracle 學習筆記（15）【DBA 進階】：閃回技術全解析

Oracle 學習筆記（15）【DBA 進階】：閃回技術全解析

人為誤操作（誤刪表、誤更新、誤提交）是資料庫營運最大風險之一。傳統備份恢復耗時長、粒度粗。Oracle 閃回技術基於 Undo 資料，提供秒級、精細粒度的還原能力，無需傳統恢復流程。本文系統梳理四大閃回類型、語法、權限需求與實戰範例。

閃回技術優勢對比

四大閃回類型對應場景

1. 閃回表 — 將表資料還原至過去時間點/SCN（誤 UPDATE/DELETE 並 COMMIT）。
2. 閃回刪除 — 從回收站恢復 DROP TABLE 的表。
3. 閃回版本查詢 — 查詢資料列的歷史版本變更軌跡。
4. 閃回事務查詢 — 審計並產生撤銷 SQL，回滾已提交事務。

一、閃回表

前置條件

• 擁有 FLASHBACK ANY TABLE 權限。
• 目標表必須啟用 列移動：ALTER TABLE t ENABLE ROW MOVEMENT;
• Undo 保留時間（UNDO_RETENTION）需覆蓋目標時間點。

語法

FLASHBACK TABLE [schema.]table_name
TO { TIMESTAMP expr | SCN expr }
[ENABLE | DISABLE] TRIGGERS;

實戰範例

-- 1. 記錄操作前 SCN
SELECT TO_CHAR(SYSDATE,'yyyy-mm-dd hh24:mi:ss') ts, TIMESTAMP_TO_SCN(SYSDATE) scn FROM DUAL;
-- 假設 SCN = 1066413

-- 2. 建表、插入、刪除、提交
CREATE TABLE flashback_table (fid NUMBER, fname VARCHAR2(20));
INSERT INTO flashback_table VALUES (1,'Tom');
INSERT INTO flashback_table VALUES (2,'Mary');
INSERT INTO flashback_table VALUES (3,'Mike');
COMMIT;
DELETE FROM flashback_table WHERE fid=2; COMMIT;

-- 3. 直接閃回失敗（未啟用行移動）
FLASHBACK TABLE flashback_table TO SCN 1066413;
-- ORA-08189

-- 4. 啟用行移動後閃回成功
ALTER TABLE flashback_table ENABLE ROW MOVEMENT;
FLASHBACK TABLE flashback_table TO SCN 1066413;
-- 閃回完成，資料恢復

注意事項

• 單一事務執行，獲取排他 DML 鎖。
• 統計資訊不會閃回；索引、觸發器等從屬物件保留。
• 不可跨越 DDL 操作；系統表不可閃回。
• 會產生 Undo/Redo，寫入告警日誌。

二、閃回刪除

回收站機制

• 僅對非 SYS 使用者生效；DROP TABLE 實為重命名移入回收站（BIN$globalUID$version）。
• PURGE RECYCLEBIN 或 DROP TABLE ... PURGE 可永久刪除。
• 管理員查看：SHOW RECYCLEBIN 或查詢 DBA_RECYCLEBIN。

語法與範例

-- 使用原表名閃回（最近被 DROP 的同名表）
FLASHBACK TABLE test3 TO BEFORE DROP;

-- 使用回收站物件名閃回（需雙引號）
FLASHBACK TABLE "BIN$V2swGcDdROGeCxvlh9SA3A==$0" TO BEFORE DROP;

-- 重名表處理：閃回並重命名
FLASHBACK TABLE test3 TO BEFORE DROP RENAME TO test3_new;

限制

• SYSTEM 表空間、字典管理表空間、審計表、虛擬私有資料庫（VPD）表不進回收站。
• 位圖索引、表先前已刪除的索引不受保護。
• 空間不足時可能被自動清除。

三、閃回版本查詢

語法

SELECT column_list
FROM table_name
VERSIONS BETWEEN {SCN | TIMESTAMP} [expr | MINVALUE]
AND [expr | MAXVALUE]
[AS OF {SCN | TIMESTAMP} expr];

關鍵偽欄位

• VERSIONS_STARTSCN / VERSIONS_STARTTIME：版本生效起點
• VERSIONS_ENDSCN / VERSIONS_ENDTIME：版本失效終點
• VERSIONS_OPERATION：I(INSERT)/U(UPDATE)/D(DELETE)
• VERSIONS_XID：關聯事務 ID

範例

SELECT vid, vname,
       versions_operation,
       versions_starttime,
       versions_endtime
FROM versions_table
VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE
ORDER BY 1,4;
/* 結果顯示：
VID VNAME     V START_TIME          END_TIME
1   Tom       I 29-MAR-12 11:07:26
2   Mary      I 29-MAR-12 11:07:41  29-MAR-12 11:08:26
2   Mary123   U 29-MAR-12 11:08:26
3   Mike      I 29-MAR-12 11:07:53
*/

四、閃回事務查詢

核心視圖：FLASHBACK_TRANSACTION_QUERY

需 SELECT ANY TRANSACTION 權限。關鍵欄位：

• XID、START_SCN/TIMESTAMP、COMMIT_SCN/TIMESTAMP
• OPERATION、TABLE_NAME、ROW_ID
• UNDO_SQL：可直接執行的撤銷語句

操作流程

1. 從閃回版本查詢取得 VERSIONS_XID。
2. 查詢 FLASHBACK_TRANSACTION_QUERY 取得 UNDO_SQL。
3. 依序執行 UNDO_SQL 完成事務回滾。

範例

-- 1. 取得事務 XID
SELECT tid, tname, versions_operation, versions_xid
FROM transaction_table
VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE;

-- 2. 查詢撤銷 SQL
SELECT operation, undo_sql
FROM flashback_transaction_query
WHERE xid = '08000F00B8010000';
/* 輸出：
OPERATION UNDO_SQL
UPDATE    update "SCOTT"."TRANSACTION_TABLE" set "TNAME"='Mary' where ROWID='AAAM6vAAEAAAALtAAB';
INSERT    delete from "SCOTT"."TRANSACTION_TABLE" where ROWID='AAAM6vAAEAAAALtAAC';
BEGIN
*/

-- 3. 執行撤銷
UPDATE "SCOTT"."TRANSACTION_TABLE" SET "TNAME"='Mary' WHERE ROWID='AAAM6vAAEAAAALtAAB';
DELETE FROM "SCOTT"."TRANSACTION_TABLE" WHERE ROWID='AAAM6vAAEAAAALtAAC';
COMMIT;

DBA 實務建議

• 規劃 Undo 表空間大小與 UNDO_RETENTION，確保閃回窗口覆蓋業務容忍度（建議 ≥ 24 小時）。
• 關鍵業務表務必 ENABLE ROW MOVEMENT。
• 定期 PURGE RECYCLEBIN 避免佔用空間；生產庫建議 DROP TABLE ... PURGE 習慣。
• 閃回事務查詢需授權謹慎，僅限資深 DBA。
• 結合 閃回資料庫（需啟用閃回日誌）可應對 TRUNCATE、大範圍誤操作等極端場景。