萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

解決 Oracle UNDO 表空間無法釋放空間問題:重建 UNDO 表空間標準流程

當 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 需實例重啟生效;整個過程需兩次重啟,建議維護窗口執行。

copyright © 萬盛學電腦網 all rights reserved