Oracle 10g UNDO 表空間暴增導致磁碟爆滿：根因分析與完整解決方案

前言

Oracle 10g 引入「自動 UNDO 保留時間調整」，雖能減少 ORA-01555 錯誤，但若配合不當，極易導致 UNDO 表空間無限膨脹、磁碟空間耗盡、資料庫掛起。本文系統分析兩大核心成因，並提供從診斷到重建的完整操作手冊。

一、核心成因深度剖析

1.1 大量事務觸發自動擴展

批次作業、大量資料載入等場景產生海量 UNDO，觸發表空間自動擴展。若 MAXSIZE 設為 UNLIMITED 或磁碟剩餘空間充足，表空間將持續增長至填滿磁碟。

1.2 長時間未提交/未收縮的大事務

• 長跑交易未 COMMIT，導致 UNDO 區塊長期處於 ACTIVE 狀態，無法被覆寫或收縮。
• 即使提交，若 UNDO_RETENTION 設過大或自動調高，UNEXPIRED 區塊也不易釋放。

1.3 10g 自動調優機制的雙刃劍

Oracle 10g 引入 TUNED_UNDORETENTION：根據實際查詢長度（MAXQUERYLEN）動態調整保留時間，可能遠超參數 UNDO_RETENTION（預設 900 秒）。

• 查詢視圖：V$UNDOSTAT.TUNED_UNDORETENTION（10g 新增，9i 無）。
• 若批次期間長查詢多，TUNED_UNDORETENTION 飆升 → UNDO 空間被長期佔用 → 空間不釋放 → 磁碟爆滿。

二、關鍵參數與視圖速查

三、三種關閉自動調優的方案（按推薦度排序）

1. 打補丁升級：10.2.0.4 版本已修復該 Bug（Metalink 420525.1），最徹底、最安全。
2. 設定 _UNDO_AUTOTUNE=FALSE：

   ALTER SYSTEM SET "_undo_autotune" = FALSE SCOPE=SPFILE;
   -- 需重啟實例生效
   

   關閉後保留時間固定為 UNDO_RETENTION，需自行評估 ORA-01555 風險。

3. 設定 _SMU_DEBUG_MODE=33554432：

   ALTER SYSTEM SET "_smu_debug_mode" = 33554432 SCOPE=SPFILE;
   

   修正演算法為 MAX(MAXQUERYLEN+300, UNDO_RETENTION)，較溫和但屬隱含參數，需謹慎。

四、緊急處理：重建 UNDO 表空間完整步驟

當磁碟已滿、資料庫掛起時，需建立新 UNDO 表空間切換、刪除舊表空間釋放空間。

4.1 環境確認

-- 1. 確認當前 UNDO 表空間
SHOW PARAMETER undo_tablespace;

-- 2. 查看資料檔大小與位置
SELECT file_name, bytes/1024/1024 AS size_mb
FROM dba_data_files
WHERE tablespace_name LIKE 'UNDOTBS%';

-- 3. 檢查回滾段使用狀況（確認無活躍事務）
SELECT s.username, u.name
FROM v$transaction t, v$rollstat r, v$rollname u, v$session s
WHERE s.taddr = t.addr
  AND t.xidusn = r.usn
  AND r.usn = u.usn
ORDER BY s.username;

-- 4. 檢查 UNDO Segment 狀態
SELECT usn, xacts, status, rssize/1024/1024 AS rssize_mb, hwmsize/1024/1024 AS hwm_mb, shrinks
FROM v$rollstat ORDER BY rssize;

4.2 建立新 UNDO 表空間並切換

-- 5. 建立新 UNDO 表空間（含自動擴展上限，防止再次失控）
CREATE UNDO TABLESPACE undotbs2
DATAFILE '/opt/oracle/oradata/ge01/UNDOTBS2.dbf'
SIZE 100M REUSE AUTOEXTEND ON NEXT 50M MAXSIZE 5000M;

-- 6. 動態切換（SCOPE=BOTH 即時生效並寫入 SPFILE）
ALTER SYSTEM SET undo_tablespace = undotbs2 SCOPE = BOTH;

4.3 等待舊 UNDO 完全離線

-- 7. 監控舊 UNDO Segment 狀態，直到全部 OFFLINE
SELECT usn, xacts, status, rssize/1024/1024, hwmsize/1024/1024, shrinks
FROM v$rollstat ORDER BY rssize;
-- 重複執行直到舊表空間所有 segment 顯示 OFFLINE

4.4 刪除舊表空間與資料檔

-- 8. 刪除舊 UNDO 表空間（含內容與資料檔）
DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;

-- 9. 驗證刪除結果
SELECT name FROM v$tablespace;

-- 10. 同步 PFILE（若使用 SPFILE 可略過）
CREATE PFILE FROM SPFILE;

-- 11. 作業系統層面確認資料檔已物理刪除
-- rm $ORACLE_BASE/oradata/$ORACLE_SID/undotbs01.dbf

五、預防性運維建議

1. 設定 MAXSIZE 上限：避免 UNLIMITED，依磁碟容量規劃合理上限。
2. 監控 V$UNDOSTAT：每 10 分鐘產生一筆統計，重點關注 TUNED_UNDORETENTION、UNDOBLKS 趨勢。
3. 批次作業時段調大 UNDO_RETENTION：如 ALTER SYSTEM SET UNDO_RETENTION=10800 SCOPE=MEMORY;，事後改回。
4. 長跑交易監控：定期查詢 V$TRANSACTION、V$SESSION_LONGOPS，及時發現阻塞事務。
5. 啟用 UNDO 表空間使用率告警：Enterprise Manager / 自腳本監控 80% 閾值告警。

六、總結

Oracle 10g UNDO 表空間暴增本質是自動調優機制與業務負載特徵不匹配。緊急時可透過「建新切換、等待離線、刪除舊」三步曲快速恢復；長遠需結合補丁升級、參數調優、監控體系建設，從根源消除風險。