Oracle 10g UNDO_RETENTION 參數深度解析：設定原理、最佳實務與保證保留

UNDO_RETENTION 核心定義

UNDO_RETENTION（單位：秒）指定**已提交交易的 UNDO 資料在 UNDO 表空間中至少保留的時間**。主要服務三大場景：

1. 讀取一致性： 長時間查詢需存取舊版本資料塊（Consistent Read）。
2. 閃回查詢： `AS OF TIMESTAMP` / `AS OF SCN` 需要歷史 UNDO。
3. 邏輯錯誤恢復： `FLASHBACK TABLE`、`FLASHBACK DATABASE` 依賴足夠保留窗口。

自動 UNDO 管理 (AUM) 三大參數

SHOW PARAMETER undo_;
NAME                 TYPE    VALUE
-----------------------------------------
undo_management      string  AUTO        -- 啟用 AUM
undo_tablespace      string  UNDOTBS1    -- 指定 UNDO 表空間
undo_retention       integer 900         -- 預設 900 秒 (15 分鐘)

保留機制關鍵差異：固定大小 vs 自動擴展

建立保證保留的 UNDO 表空間

CREATE UNDO TABLESPACE undotbs_guar
DATAFILE '/u01/app/oracle/oradata/undotbs_guar01.dbf'
SIZE 500M AUTOEXTEND ON MAXSIZE 10G
RETENTION GUARANTEE;

-- 或事後修改既有表空間
ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;
-- 取消保證
ALTER TABLESPACE undotbs1 RETENTION NOGUARANTEE;

UNDO_RETENTION 設定建議值

精確校準法： 查詢 V$UNDOSTAT.MAXQUERYLEN（近期最長查詢秒數），設定值 ≥ 該值。

空間規劃：Undo Advisor 實務流程

1. 建立小尺寸 UNDO 表空間（如 500M）+ AUTOEXTEND ON。
2. 運行代表性負載 24-48 小時（含尖峰時段）。
3. OEM → Undo Management → Undo Advisor，輸入期望保留時間（如 24h），取得建議大小。
4. 依建議調整表空間或 MAXSIZE。

空間壓力預警機制（含 GUARANTEE 時）

• 85% 使用率 → Tablespace Warning Alert
• 97% 使用率 → Tablespace Critical Alert
• 100% → 所有 DML 失敗 (ORA-30036)，DDL 仍可執行

結語

UNDO_RETENTION 非單純參數，而是「保留期望」與「空間資源」的博弈。生產環境**強烈建議**：UNDO 表空間啟用 AUTOEXTEND + 設定合理 MAXSIZE + 關鍵業務啟用 RETENTION GUARANTEE，並配合 Undo Advisor 定期覆核空間大小，才能在「讀取一致性」、「閃回功能」與「DML 可用性」三者間取得平衡。