當 UNDOTBS 表空間空間持續增長無法自動回收,標準解決方案是重建 UNDO 表空間:
-- 1. 創建備用 UNDO 表空間
CREATE UNDO TABLESPACE newundotbs
DATAFILE '$ORA_DATA/newundotbs01.dbf' SIZE 1000M;
-- 2. 切換至新 UNDO
ALTER SYSTEM SET undo_tablespace=newundotbs SCOPE=SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP;
-- 3. 刪除舊 UNDO(含內容與數據文件)
DROP TABLESPACE undotbs INCLUDING CONTENTS AND DATAFILES;
-- 4. 重建原名 UNDO 表空間
CREATE UNDO TABLESPACE undotbs
DATAFILE '$ORA_DATA/undotbs01.dbf' SIZE 1000M;
-- 5. 切回原 UNDO
ALTER SYSTEM SET undo_tablespace=undotbs SCOPE=SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP;
-- 6. 清理備用 UNDO
DROP TABLESPACE newundotbs INCLUDING CONTENTS AND DATAFILES;
注意:SCOPE=SPFILE 需實例重啟生效;整個過程需兩次重啟,建議維護窗口執行。