在线迁移UNDO表空间(只能在线迁移)

1 确认当前UNDO表空间

SQL> show parameter undo

NAME                                 TYPE        VALUE

------------------------------------ ----------- -----------

undo_management                      string      AUTO

undo_retention                       integer     900

undo_tablespace                      string      UNDOTBS1

2 创建新UNDO表空间

SQL> create undo tablespace undotbs2 datafile '/oradata/test/undotbs2.dbf' size 512m autoextend on next 10m maxsize unlimited;

Tablespace created.

3 修改默认UNDO表空间

SQL> alter system set undo_tablespace = undotbs2;

System altered.

SQL> show parameter undo

NAME                                 TYPE        VALUE

------------------------------------ ----------- -----------

undo_management                      string      AUTO

undo_retention                       integer     900

undo_tablespace                      string      UNDOTBS2

4 删除原来的UNDO表空间

4.1 若成功删除

SQL> drop tablespace undotbs1 including contents and datafiles cascade constraints;

Tablespace dropped.

4.2 未成功删除

有时会出现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

_SYSSMU15$                     PUBLIC          UNDOTBS1                       ONLINE

 

这是因为原undo表空间还有活动事务, 还在迁移中. 观察alert.log有

Tue Jun 21 14:21:52 2011

Undo Tablespace 1 moved to Pending Switch-Out state.

 

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

Tue Jun 21 14:21:54 2011

Successfully onlined Undo Tablespace 5.

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

 

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

Tablespace dropped.