作業系統版本及資料庫版本如下：
SQL> !cat /etc/redhat-release
Red Hat Enterprise Linux Server release 6.5 (Santiago)
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
查看當前使用的undo資料表空間資訊
SQL> show parameter undo_tablespace
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string UNDOTBS1
SQL> select tablespace_name, file_id, file_name,round (bytes / (1024 * 1024), 0) total_space from dba_data_files where tablespace_name='UNDOTBS1';
TABLESPACE_NAME FILE_ID FILE_NAME TOTAL_SPACE
------------------------------ ---------------------------------------- ---------------------------------------- ----------
UNDOTBS1 3 /u01/app/oracle/oradata/orcl/undotbs01.dbf 60
1、資料庫狀態靜止時（無DML操作期間）執行UNDO資料表空間切換（由UNDOTBS1切換為UNDOTBS2）
（1）建立新的undo資料表空間UNDOTBS2
SQL> create undo tablespace UNDOTBS2 datafile '/u01/app/oracle/oradata/orcl/undotbs02.dbf' size 10M;
Tablespace created.
（2）切換UNDOTBS2為新的undo資料表空間
SQL> alter system set undo_tablespace = undotbs2 scope=both;
System altered.
（3）此時資料庫處於靜止狀態，無任何DML操作，查看UNDOTBS1已經處於OFFLINE狀態
SQL> select tablespace_name , status , count(*) from dba_rollback_segs group by tablespace_name , status;
TABLESPACE_NAME STATUS COUNT(*)
------------------------------ ---------------- ----------
UNDOTBS1 OFFLINE 10
SYSTEM ONLINE 1
UNDOTBS2 ONLINE 10
（4）檢查確認UNDOTBS1中沒有ONLINE的segment
SQL> select status,segment_name from dba_rollback_segs where status not in ('OFFLINE') and tablespace_name='UNDOTBS1';
no rows selected
（5）刪除舊的UNDOTBS1
SQL> Drop tablespace UNDOTBS1 including contents and datafiles;
Tablespace dropped.
（6）至此，undo資料表空間由UNDOTBS1成功切換為UNDOTBS2.
2、資料庫中有DML操作期間，切換UNDO資料表空間步驟（由UNDOTBS2切換為UNDOTBS1）
session 1正在執行如下操作：
conn test/test
create table test (name varchar(2));
insert into test values ('zhangsan');
commit;
update test set name='lisi' where name='zhangsan';
此時未提交
session 2開始切換undo資料表空間操作
（1）確認當前使用的undo資料表空間
SQL> show parameter undo_tablespace
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string UNDOTBS2
（2）查看當前undo資料表空間的所有SELMENT均為ONLINE狀態
SQL> select status,segment_name from dba_rollback_segs where status not in ('OFFLINE') and tablespace_name='UNDOTBS2';
STATUS SEGMENT_NAME
---------------- ------------------------------
ONLINE _SYSSMU11_3423735372$
ONLINE _SYSSMU12_567660877$
ONLINE _SYSSMU13_100563780$
ONLINE _SYSSMU14_1447748955$
ONLINE _SYSSMU15_478708454$
ONLINE _SYSSMU16_3309423900$
ONLINE _SYSSMU17_525951688$
ONLINE _SYSSMU18_130984470$
ONLINE _SYSSMU19_3964826557$
ONLINE _SYSSMU20_994913344$
10 rows selected.
（3）建立新的undo資料表空間UNDOTBS1
SQL> create undo tablespace UNDOTBS1 datafile '/u01/app/oracle/oradata/orcl/undotbs01.dbf' size 10M;
Tablespace created.
（4）設定UNDOTBS1為預設undo資料表空間
SQL> alter system set undo_tablespace = undotbs1 scope=both;
System altered.
（5）此時檢查UNDOTBS2中任然有一個SEGMENT處於ONLINE狀態
SQL> select tablespace_name , status , count(*) from dba_rollback_segs group by tablespace_name , status;
TABLESPACE_NAME STATUS COUNT(*)
------------------------------ ---------------- ----------
UNDOTBS1 ONLINE 10
SYSTEM ONLINE 1
UNDOTBS2 OFFLINE 9
UNDOTBS2 ONLINE 1
SQL> select status,segment_name from dba_rollback_segs where status not in ('OFFLINE') and tablespace_name='UNDOTBS2';
STATUS SEGMENT_NAME
---------------- ------------------------------
ONLINE _SYSSMU15_478708454$
故此時不能盲目刪除UNDOTBS2資料表空間，否則將報錯或導致資料不一致；甚至會導致資料庫故障（或許~大概~有可能。。。）
此時需等待UNDOTBS2變為OFFLINE後才可執行刪除該資料表空間的操作。
等待ing...
（6）此時回到session 1執行commit;，再回到session 2查看UNDOTBS2的狀態變為OFFLINE
SQL> select status,segment_name from dba_rollback_segs where status not in ('OFFLINE') and tablespace_name='UNDOTBS2';
no rows selected
SQL> select tablespace_name , status , count(*) from dba_rollback_segs group by tablespace_name , status;
TABLESPACE_NAME STATUS COUNT(*)
------------------------------ ---------------- ----------
UNDOTBS1 ONLINE 10
SYSTEM ONLINE 1
UNDOTBS2 OFFLINE 10
（7）此時刪除UNDOTBS2即可
SQL> Drop tablespace UNDOTBS2 including contents and datafiles;
Tablespace dropped.
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
（8）至此，undo資料表空間由UNDOTBS2成功切換為UNDOTBS1
參考文章：IF: How to Switch to a New Undo Tablespace (文檔 ID 1951695.1)
