1.查看數據庫undo表空間的dbf文件是否大于25G，如果大于我們則重建。
-rw-r----- 1 oracle oinstall 26G 2011-05-10 10:10 /opt/oracle/oradata/CMCCWAP/undotbs02.dbf
2.查看數據庫的默認UNDO表空間（紅色部分顯示為UNDOTBS2，說明當前使用的是表空間為：UNDOTBS2）
SQL> show parameter undo;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS2
3.創建新的UNDO空間UNDOTB1
SQL> create undo tablespace UNDOTBS1 datafile '/opt/oracle/oradata/CMCCWAP/undotbs01.dbf' size 200m autoextend on;
4.將數據庫的默認表空間切換到新創建的表空間
SQL> alter system set undo_tablespace=UNDOTBS1 scope=both;
5.查看數據庫的默認表空間是否已經切換到新創建的表空間，如下紅色部分使用的是UNDOTBS1，說明已經遷移到新的表空間
SQL> show parameter undo;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
6.確保所有在UNDOTBS2的undo segment都已offline,如下紅色部分表示還未offline
SQL> select SEGMENT_NAME ,STATUS ,TABLESPACE_NAME from dba_rollback_segs;
SEGMENT_NAME STATUS TABLESPACE_NAME
------------------------------ ---------------- ------------------------------
SYSTEM ONLINE SYSTEM
_SYSSMU1$ OFFLINE UNDOTBS2
_SYSSMU2$ OFFLINE UNDOTBS2
_SYSSMU3$ OFFLINE UNDOTBS2
_SYSSMU4$ OFFLINE UNDOTBS2
_SYSSMU5$ OFFLINE UNDOTBS2
_SYSSMU6$ OFFLINE UNDOTBS2
_SYSSMU7$ ONLINE UNDOTBS2
_SYSSMU8$ OFFLINE UNDOTBS2
_SYSSMU9$ OFFLINE UNDOTBS2
_SYSSMU10$ OFFLINE UNDOTBS2
SEGMENT_NAME STATUS TABLESPACE_NAME
------------------------------ ---------------- ------------------------------
_SYSSMU11$ OFFLINE UNDOTBS2
_SYSSMU12$ ONLINE UNDOTBS1
_SYSSMU13$ ONLINE UNDOTBS1
_SYSSMU14$ ONLINE UNDOTBS1
_SYSSMU15$ ONLINE UNDOTBS1
_SYSSMU16$ ONLINE UNDOTBS1
17 rows selected.
當oracle在沒有undo tablespace時會使用system表空間作為undo tablespace.
7.當上述UNDOTBS2全部offline后，刪除原來的UNDO tablespace(如果不刪除在備份時會報錯)
SQL> drop tablespace UNDOTBS2 including contents and datafiles;
drop tablespace undotbs1 including contents and datafiles
*
ERROR at line 1:
ORA-01116: error in opening database file 2
ORA-01110: data file 2: '/opt/oracle/oradata/CMCCWAP/undotbs02.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
offline data file 2
SQL> alter database datafile '/opt/oracle/oradata/CMCCWAP/undotbs02.dbf' offline;
Database altered.
刪除
SQL> drop tablespace undotbs1 including contents and datafiles;免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
