--1. 确认当前的UNDO表空间

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> show parameter undo
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     86400
undo_tablespace                      string      UNDOTBS1

--2. 创建新的UNDO表空间

SQL> CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE '/u06/oradata/ESUITE/undotbs2.dbf'
size 4096M reuse autoextend on next 10M maxsize unlimited;

--3. 修改默认UNDO表空间的标识

SQL> ALTER SYSTEM SET UNDO_TABLESPACE = UNDOTBS2;
SQL> show parameter undo
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     86400
undo_tablespace                      string      UNDOTBS2

--3. 删除原来的UNDO表空间

SQL> DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use

--提示ORA-30013错误, 检查是否还有在UNDOTBS1表空间上online状态的回滚段:

SQL> select segment_name,owner,tablespace_name,status from dba_rollback_segs
where tablespace_name='UNDOTBS1' and status = 'ONLINE';
SEGMENT_NAME                   OWNER           TABLESPACE_NAME                STATUS
------------------------------ --------------- ------------------------------ ----------
_SYSSMU13$                     PUBLIC          UNDOTBS1                       ONLINE
_SYSSMU28$                     PUBLIC          UNDOTBS1                       ONLINE

--果然还有, 过会儿alert.log有如下提示, 因为原undo表空间还有活动事务, 还在迁移中:

Wed Jun 24 18:06:02 2009
Undo Tablespace 1 moved to Pending Switch-Out state.

--等到alert.log出现如下提示后, 表示undo表空间迁移完毕:

Wed Jun 24 18:16:02 2009
Undo Tablespace 1 successfully switched out.

--此时再看是否还有在UNDOTBS1表空间上online状态的回滚段:

SQL> select segment_name,owner,tablespace_name,status from dba_rollback_segs
where tablespace_name='UNDOTBS1' and status = 'ONLINE';

no rows selected

--此时虽然还有offline状态的回滚段, 但可以删除原来的UNDO表空间了:

SQL> DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

Tablespace dropped.

SQL> select segment_name,owner,tablespace_name,status from dba_rollback_segs
where tablespace_name='UNDOTBS1';

no rows selected

--END--