解決 Oracle UNDO 表空間暴增無法釋放：重建 UNDO 表空間標準流程

問題現象

UNDO 表空間 (UNDOTBS) 持續增長，即使執行 SHRINK 或調整 UNDO_RETENTION 仍無法釋放空間，佔用大量磁碟。

根本原因

• 長時間運行的查詢導致 UNDO 保留過久。
• UNDO 區段因高水位線 (HWM) 無法縮減。
• 極大交易產生的 UNDO 區塊未被及時覆寫。

標準解決方案：離線重建 UNDO 表空間

-- 1. 建立備用 UNDO 表空間
CREATE UNDO TABLESPACE newundotbs 
DATAFILE '$ORA_DATA/newundotbs01.dbf' SIZE 1000M;

-- 2. 切換至新 UNDO (需修改 SPFILE)
ALTER SYSTEM SET undo_tablespace = newundotbs SCOPE=SPFILE;

-- 3. 重啟資料庫使設定生效
SHUTDOWN IMMEDIATE;
STARTUP;

-- 4. 刪除舊 UNDO 表空間 (含資料檔)
DROP TABLESPACE undotbs INCLUDING CONTENTS AND DATAFILES;

-- 5. 重建原名 UNDO 表空間
CREATE UNDO TABLESPACE undotbs 
DATAFILE '$ORA_DATA/undotbs01.dbf' SIZE 1000M;

-- 6. 切回原 UNDO
ALTER SYSTEM SET undo_tablespace = undotbs SCOPE=SPFILE;

-- 7. 再次重啟
SHUTDOWN IMMEDIATE;
STARTUP;

-- 8. 清理備用 UNDO
DROP TABLESPACE newundotbs INCLUDING CONTENTS AND DATAFILES;

關鍵注意事項

• 需兩次重啟：切換 UNDO 表空間屬靜態參數變更，必須重啟實例。
• 確保無作用中交易：執行前確認無長跑交易，否則 DROP TABLESPACE 會報錯 (ORA-01548)。可用 V$TRANSACTION 確認。
• 保留 UNDO_RETENTION：重建後記恢復原 UNDO_RETENTION 設定。
• 備份優先：操作前務必完成資料庫完整備份 (RMAN)。

替代方案 (不重啟，12c+)

Oracle 12c 引入 Online UNDO Tablespace Switch，可在線切換 UNDO 表空間無需重啟，但舊 UNDO 仍需待所有交易結束才能刪除。