oracle undo表空间重建步骤

（1）查看数据库的undo表空间信息

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

（2）创建新的undo表空间UNDOTBS2

create undo tablespace UNDOTBS2 datafile '/U01/app/oracle/oradata/testdb/undotbs201.dbf' size 1G autoextend on;

（3）将数据库的默认表空间切换到新创建的表空间

alter system set undo_tablespace=UNDOTBS2 scope=both;

（4）查看数据库的默认表空间是否已经切换到新创建的表空间

show parameter undo
NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
undo_management 		     string	 AUTO
undo_retention			     integer	 900
undo_tablespace 		     string	 UNDOTBS2

（5）确保所有在UNDOTBS1的undo segment都已offline

select SEGMENT_NAME ,STATUS ,TABLESPACE_NAME from dba_rollback_segs;

当oracle在没有undo tablespace时会使用system表空间作为undo tablespace.

（6）当上述UNDOTBS1全部offline后，删除原来的UNDO tablespace

alter database datafile '/U01/app/oracle/oradata/testdb/undotbs01.dbf' offline;
drop tablespace UNDOTBS1 including contents and datafiles;