Oracle 10g 回溯版本查詢：追蹤資料列變更歷史的利器

痛點

多應用共享資料表時，資料列可能在「你最不期望的時候」被修改、刪除。傳統靠 LogMiner 分析歸檔日誌雖可行但門檻高、流程繁瑣。

回溯版本查詢

10g 引入，基於自動 UNDO 管理與 UNDO 表空間，直接在 SQL 中查詢資料列歷史版本。

語法

SELECT ... FROM table_name
VERSIONS BETWEEN { SCN | TIMESTAMP } { expr | MINVALUE } AND { expr | MAXVALUE }
...

• 置於表名後、別名前，可對查詢中不同表指定不同時點範圍。
• 最遠可回溯至 UNDO_RETENTION 參數設定值或最近 DDL (CREATE/ALTER/DROP) 之前，以較早者為準。

實戰案例：PARTS 表描述欄位「編輯戰」

SELECT versions_starttime, versions_endtime,
       versions_xid, versions_operation,
       part_id, description
FROM   parts
VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE
WHERE  part_id = 12345
ORDER BY versions_starttime;

關鍵偽欄位：

• VERSIONS_STARTTIME / ENDTIME：版本生效/失效時間。
• VERSIONS_STARTSCN / ENDSCN：對應 SCN。
• VERSIONS_XID：交易 ID，可關聯同一交易修改的其他表/行。
• VERSIONS_OPERATION：I (Insert)、U (Update)、D (Delete)。

回溯交易查詢 — 追查「誰動的手」

SELECT xid, start_scn, commit_scn,
       operation, logon_user, undo_sql
FROM   flashback_transaction_query
WHERE  xid = HEXTORAW('08001200ABC123');  -- 取自 VERSIONS_XID

• 顯示交易提交順序、登入用戶、甚至可執行的回滾 SQL (UNDO_SQL)。
• 注意 ROW_ID 欄位僅為視圖顯示用，非 ROWID 偽欄位。

前置條件與限制

1. 資料庫必須啟用自動 UNDO 管理 (UNDO_MANAGEMENT=AUTO) 並建立足夠大小的 UNDO 表空間。
2. 設定適當 UNDO_RETENTION (秒)，保證歷史版本保留窗口。
3. 表不可有 NOPARALLEL、NOCACHE 等阻礙版本生成的屬性 (罕見)。
4. DDL 操作會截斷該物件的版本歷史。

應用場景

• 審計追蹤：關鍵業務表變更歷史完整記錄。
• 邏輯錯誤快速定位：誤刪/誤改數分鐘內自助恢復 (FLASHBACK TABLE ... TO TIMESTAMP)。
• 應用除錯：重現「資料怎麼變成這樣的」全過程。
• 合規報告：滿足 SOX、GDPR 等法規要求的資料血緣追溯。

結語

回溯版本查詢 + 回溯交易查詢，將原本需要 DBA 深度介入的日誌分析工作，降維為開發人員可直接使用的 SQL 功能。善用 UNDO 保留窗口，讓資料庫擁有「時光機」能力。