Undo 檔案損壞/遺失、無備份、需快速開啟資料庫。代價: 若有未提交交易將丟失 (通常可接受);若無未提交交易則零資料遺失。
ALTER DATABASE DATAFILE 'I:\INTEL_DATA\O06DMS0\UNDO1.O06DMS0' OFFLINE DROP;
ALTER DATABASE OPEN;
SELECT segment_name, status FROM dba_rollback_segs;
-- 記下所有 OFFLINE 的 _SYSSMU...$ 區段名稱
CREATE PFILE='H:\initO06DMS0.ora' FROM SPFILE;
修改 PFILE:
*.undo_management='MANUAL'
*.undo_tablespace='UNDO2'
*._offline_rollback_segments=('_SYSSMU10_1201331463$','_SYSSMU9_2926456744$', ...)
以新 PFILE 啟動:
STARTUP PFILE='H:\initO06DMS0.ora'
DROP ROLLBACK SEGMENT "_SYSSMU10_1201331463$";
... (依序刪除所有 OFFLINE 段)
DROP TABLESPACE undo1 INCLUDING CONTENTS;
CREATE UNDO TABLESPACE undo2
DATAFILE 'I:\INTEL_DATA\O06DMS0\UNDO2.O06DMS0' SIZE 100M;
CREATE SPFILE FROM PFILE='H:\initO06DMS0.ora';
SHUTDOWN IMMEDIATE;
STARTUP; -- 使用原 SPFILE
ALTER SYSTEM SET UNDO_MANAGEMENT='AUTO' SCOPE=SPFILE;
ALTER SYSTEM RESET "_OFFLINE_ROLLBACK_SEGMENTS" SCOPE=SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP;
-- 驗證
SHOW PARAMETER UNDO;
SELECT segment_name, status FROM dba_rollback_segs;