Oracle Undo 表空間縮減實戰：建立新 Undo 切換並釋放 27GB 空間完整流程

問題現場

• 環境：RHEL 4 + Oracle 10.2.0.1.0 (ASM 儲存)
• 症狀：ORA-01653: unable to extend table SYSMAN.MGMT_SYSTEM_ERROR_LOG ... in tablespace SYSAUX
• 根因：磁碟空間被 UNDOTBS1 (27GB) + TEMP (20GB) 佔滿，導致 SYSAUX 無法擴展。

解決策略：建立新 Undo 表空間 → 切換 → 等待舊 Undo 離線 → 刪除舊 Undo

詳細操作步驟

1. 確認現有 Undo 資料檔

SELECT file_name, bytes/1024/1024 MB
FROM dba_data_files
WHERE tablespace_name = 'UNDOTBS1';

-- 結果範例：
-- +ORADG/danaly/datafile/undotbs1.265.600173875   27810 MB

2. 檢查 Undo Segment 狀態

SELECT usn, xacts, 
       rssize/1024/1024/1024 GB_RSSIZE,
       hwmsize/1024/1024/1024 GB_HWM,
       shrinks
FROM v$rollstat
ORDER BY rssize;

觀察 XACTS (作用中交易數) 與 STATUS (稍後步驟需用)。

3. 建立新 Undo 表空間

-- ASM 環境自動配置大小，亦可指定 SIZE
CREATE UNDO TABLESPACE undotbs2;

-- 非 ASM 需指定資料檔
-- CREATE UNDO TABLESPACE undotbs2 DATAFILE '/u01/app/oracle/oradata/undotbs2_01.dbf' SIZE 1G AUTOEXTEND ON;

4. 切換 Undo 表空間 (動態生效 + 寫入 SPFILE)

ALTER SYSTEM SET undo_tablespace = undotbs2 SCOPE = BOTH;

SCOPE=BOTH 同時修改記憶體參數與 SPFILE，避免重啟後參數衝突。

5. 等待舊 Undo 所有 Segment 狀態轉為 OFFLINE

SELECT usn, xacts, status,
       rssize/1024/1024/1024 GB_RSSIZE,
       hwmsize/1024/1024/1024 GB_HWM
FROM v$rollstat
ORDER BY rssize;

關鍵觀察 STATUS 欄位：

• ONLINE：正在使用或可用
• PENDING OFFLINE：等待現有交易結束後離線
• OFFLINE：已完全離線，可安全刪除

執行上述查詢直到 UNDOTBS1 所有 Segment 顯示 OFFLINE (或從 V$ROLLSTAT 消失)。通常需數秒至數分鐘，視作用中長交易而定。

6. 刪除舊 Undo 表空間 (含資料檔)

DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;

• INCLUDING CONTENTS：刪除段內物件
• AND DATAFILES：同時刪除 ASM/Datafile 實體檔案，徹底釋放磁碟空間

7. 驗證空間釋放 (ASM 環境)

[oracle@danaly ~]$ export ORACLE_SID=+ASM
[oracle@danaly ~]$ asmcmd
ASMCMD> du
Used_MB   Mirror_used_MB
  21625              21625
ASMCMD> exit

Used_MB 大幅下降，確認 27GB 空間已釋放。

關鍵風險與應對

後續優化建議

1. 固定 Undo 大小：觀察穩態 Undo 使用量 (如 2~4GB)，設定 UNDO_TABLESPACE 固定大小並關閉 AUTOEXTEND，避免再次暴增。
2. 設定 UNDO_RETENTION：依業務需求設定保留時間 (如 900 秒)，配合 RETENTION GUARANTEE 保護閃回查詢。
3. 監控 Temp 表空間：同樣 20GB Temp 可能因大型排序/Hash Join 暴增，建議限制大小並啟用 TEMP_UNDO_ENABLED = TRUE (12c+) 將臨時 Undo 導向 Temp。

結語

透過「建新→切換→等待離線→刪除含檔案」四步驟，可安全、快速回收異常膨脹的 Undo 表空間。關鍵在於耐心等待 PENDING OFFLINE 轉為 OFFLINE，並使用 AND DATAFILES 徹底釋放儲存空間。