Oracle UNDO 表空間釋放與重構完整操作指南

大量 DML 導致 UNDO 表空間膨脹的釋放與重構實戰

當大批量 DML 操作導致 UNDO 表空間擴展至數十 GB 且物理空間不被釋放時，可通過創建新 UNDO 表空間、切換、舊段離線、刪除舊表空間並手動清理文件的方式完成重構。同樣適用於 TEMP 表空間暴增場景。

一、現狀排查與準備

1. 查看所有表空間名稱

SELECT name FROM v$tablespace;

2. 檢查目標 UNDO 表空間大小（以 UNDOTBS1 為例）

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

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, rssize/1024/1024/1024 AS rssize_gb,
       hwmsize/1024/1024/1024 AS hwmsize_gb, shrinks
FROM v$rollstat
ORDER BY rssize;

二、創建新 UNDO 表空間並切換

CREATE UNDO TABLESPACE undotbs2
DATAFILE 'D:\Oracle\PRODUCT\10.1.0\ORADATA\ORCL\UNDOTBS02.DBF'
SIZE 10M REUSE AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

動態修改參數生效（SPFILE 永久生效）

ALTER SYSTEM SET undo_tablespace = undotbs2 SCOPE = BOTH;

三、等待舊 UNDO Segment 全部 OFFLINE

持續監控 v$rollstat 中 status 列，直到所有屬於 undotbs1 的段顯示 OFFLINE 或 PENDING OFFLINE：

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

注意：此過程可能需數分鐘至數小時，視活躍事務長短而定。生產環境請安排維護窗口。

四、刪除舊 UNDO 表空間並清理物理文件

1. 刪除表空間（含內容與數據文件邏輯關聯）：

   DROP TABLESPACE undotbs1 INCLUDING CONTENTS;

2. 驗證刪除結果：

   SELECT name FROM v$tablespace;

3. 關鍵步驟：重啟資料庫或服務器後，至原數據文件路徑手動刪除 UNDOTBS01.DBF 物理文件。

   原因：DROP TABLESPACE ... INCLUDING CONTENTS 僅移除數據字典關聯，不會自動刪除作業系統層面的物理文件。

五、同樣適用於 TEMP 表空間重構

1. 創建新臨時表空間：CREATE TEMPORARY TABLESPACE temp2 TEMPFILE ...
2. 修改默認臨時表空間：ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;
3. 等待舊 TEMP 空間無活躍排序操作後刪除：DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;（臨時表空間可直接帶 AND DATAFILES 自動刪除物理文件）。

六、最佳實踐建議

• 為 UNDO 表空間設置合理的 UNDO_RETENTION 與 MAXSIZE，防止無限增長。
• 啟用 UNDO TABLESPACE 自動擴展並設置上限，配合 RETENTION GUARANTEE 保護關鍵回滾段。
• 定期監控 v$undostat 與 dba_hist_undostat 評估空間需求。
• 大批量操作前預估 UNDO 需求，必要時分批提交或使用 APPEND 模式減少 UNDO 產生。

通過「新建→切換→待離線→刪除→清理文件」的標準化流程，可安全、徹底地回收 UNDO/TEMP 表空間佔用的物理磁盤空間，避免磁盤耗盡導致資料庫掛起。