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;
