oracle重建UNDO表空间

1.查看数据库undo表空间的dbf文件是否大于25G，如果大于我们则重建。

创新互联主营游仙网站建设的网络公司,主营网站建设方案,重庆App定制开发,游仙h5微信平台小程序开发搭建,游仙网站营销推广欢迎游仙等地区企业咨询

-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.