Oracle UNDO 表空間釋放與重構實戰指南：解決大量 DML 導致磁盤空間不足

問題現象

執行大批量 DML（如批量更新、刪除、導入）後，UNDO 表空間（或臨時表空間 TEMP）膨脹至數十 GB，即使事務提交、Checkpoint 完成，數據文件物理大小也不會自動縮減，導致磁盤空間告警甚至寫入失敗。本文提供標準化的 UNDO 表空間重構流程，同樣適用於 TEMP 表空間。

核心原理

• UNDO 表空間採用 本地管理 + 自動擴展，High Water Mark (HWM) 只升不降。
• DROP TABLESPACE 僅刪除數據字典關聯，不會自動刪除作業系統層面的數據文件，需手動清理。
• 重構核心思路：創建新 UNDO → 切換實例參數 → 等待舊 Undo Segment 全部 OFFLINE → 刪除舊表空間及文件。

操作前準備

1. 確認無活躍長事務：生產環境建議在業務低峰期操作。
2. 備份關鍵信息：記錄舊 UNDO 表空間名、文件路徑、大小。
3. 權限：需 SYSDBA 或 ALTER TABLESPACE、CREATE TABLESPACE、DROP TABLESPACE 權限。

分步操作流程

1. 查看現有表空間與文件

-- 表空間列表
SELECT tablespace_name, contents, extent_management, allocation_type
FROM dba_tablespaces
WHERE contents = 'UNDO';

-- 文件詳情
SELECT file_name, bytes/1024/1024 MB, autoextensible, maxbytes/1024/1024 MAX_MB
FROM dba_data_files
WHERE tablespace_name = 'UNDOTBS1';

2. 檢查回滾段使用情況（確認無活躍事務）

SELECT s.username, r.name segment_name, t.start_time, t.used_ublk
FROM v$transaction t
JOIN v$rollstat r ON t.xidusn = r.usn
JOIN v$session s ON t.addr = s.taddr
ORDER BY t.start_time;

若有活躍事務，建議等待提交或協調應用端終止。

3. 檢查 Undo Segment 狀態與大小

SELECT usn, xacts, status,
       rssize/1024/1024/1024 rssize_gb,
       hwmsize/1024/1024/1024 hwm_gb,
       shrinks
FROM v$rollstat
ORDER BY rssize DESC;

記錄當前 status 為 ONLINE 的段數量。

4. 創建新 UNDO 表空間（啟用自動擴展）

CREATE UNDO TABLESPACE undotbs2
DATAFILE 'D:\Oracle\ORADATA\ORCL\undotbs02.dbf'
SIZE 10M REUSE
AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

• SIZE 10M：初始小文件，避免瞬間佔用大量空間。
• AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED：按需增長，防止 ORA-01652。

5. 動態切換實例使用的 UNDO 表空間

ALTER SYSTEM SET undo_tablespace = undotbs2 SCOPE = BOTH;

• SCOPE=BOTH：同時修改內存（SPFILE）與運行實例，立即生效並持久化。
• 新事務將分配在 undotbs2，舊 undotbs1 的 Segment 逐漸變為 OFFLINE 狀態（待活躍事務結束）。

6. 等待舊 UNDO 表空間所有 Segment 變為 OFFLINE

-- 定期輪詢，直到無 ONLINE 段
SELECT usn, xacts, status,
       rssize/1024/1024/1024 rssize_gb
FROM v$rollstat
WHERE status = 'ONLINE'
ORDER BY rssize DESC;

視事務長短，可能需等待數分鐘至數小時。可通過 v$transaction 監控殘留事務。

7. 確認新 UNDO 表空間 Segment 已 ONLINE

SELECT usn, xacts, status, rssize/1024/1024/1024 rssize_gb
FROM v$rollstat
WHERE status = 'ONLINE';

8. 刪除舊 UNDO 表空間（含內容與數據文件）

DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;

• INCLUDING CONTENTS：刪除段元數據。
• AND DATAFILES：關鍵參數，同時刪除作業系統層面的 .dbf 文件，釋放磁盤空間。若省略此參數，需手動刪除文件。

9. 驗證刪除結果

SELECT tablespace_name FROM dba_tablespaces WHERE contents = 'UNDO';

應僅剩 undotbs2。

同樣適用於 TEMP 表空間重構

-- 1. 創建新 TEMP
CREATE TEMPORARY TABLESPACE temp2
TEMPFILE 'D:\Oracle\ORADATA\ORCL\temp02.dbf'
SIZE 10M REUSE AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

-- 2. 切換默認臨時表空間
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

-- 3. 等待舊 TEMP 無活躍排序/雜湊操作（查詢 v$sort_usage）
-- 4. 刪除舊 TEMP
DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

最佳實踐建議

1. 監控告警：設置表空間使用率 > 80% 告警，預留擴展時間。
2. 保留保障：ALTER TABLESPACE undotbs2 RETENTION GUARANTEE; 防止長查詢 ORA-01555，但會限制空間復用，視業務權衡。
3. 定期檢查 HWM：SELECT tablespace_name, max(bytes)/1024/1024 max_mb FROM dba_data_files GROUP BY tablespace_name; 關注未釋放的大文件。
4. 大批量操作分批提交：避免單一事務撐爆 UNDO。

結語

UNDO/TEMP 表空間膨脹是大數據量操作的常見副作用。通過「新建 → 切換 → 等待 OFFLINE → 刪除含文件」標準化流程，可安全、無停機地回收磁盤空間。關鍵在於耐心等待舊 Segment 自然 OFFLINE，切勿強制刪除導致事務失敗或實例不穩定。