Oracle切换undo表空间操作步骤（转）

操作系统版本及数据库版本如下：
SQL> !cat /etc/redhat-release
Red Hat Enterprise Linux Server release 6.5 (Santiago)

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production


查看当前使用的undo表空间信息
SQL> show parameter undo_tablespace
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace                      string      UNDOTBS1

SQL> select tablespace_name, file_id, file_name,round (bytes / (1024 * 1024), 0) total_space from dba_data_files where tablespace_name='UNDOTBS1';
TABLESPACE_NAME                   FILE_ID       FILE_NAME                                     TOTAL_SPACE
------------------------------ ---------------------------------------- ---------------------------------------- ----------
UNDOTBS1                                3        /u01/app/oracle/oradata/orcl/undotbs01.dbf    60


1、数据库状态静止时（无DML操作期间）执行UNDO表空间切换（由UNDOTBS1切换为UNDOTBS2）
（1）创建新的undo表空间UNDOTBS2
SQL>  create undo tablespace UNDOTBS2 datafile '/u01/app/oracle/oradata/orcl/undotbs02.dbf' size 10M;
Tablespace created.

（2）切换UNDOTBS2为新的undo表空间
SQL> alter system set undo_tablespace = undotbs2 scope=both;
System altered.

（3）此时数据库处于静止状态，无任何DML操作，查看UNDOTBS1已经处于OFFLINE状态
SQL> select tablespace_name , status , count(*) from dba_rollback_segs group by tablespace_name , status;
TABLESPACE_NAME                STATUS             COUNT(*)
------------------------------ ---------------- ----------
UNDOTBS1                       OFFLINE                  10
SYSTEM                         ONLINE                    1
UNDOTBS2                       ONLINE                   10

（4）检查确认UNDOTBS1中没有ONLINE的segment
SQL> select status,segment_name from dba_rollback_segs where status not in ('OFFLINE') and tablespace_name='UNDOTBS1';
no rows selected

（5）删除旧的UNDOTBS1
SQL> Drop tablespace UNDOTBS1 including contents and datafiles;
Tablespace dropped.

（6）至此，undo表空间由UNDOTBS1成功切换为UNDOTBS2 .


2、数据库中有DML操作期间，切换UNDO表空间步骤（由UNDOTBS2切换为UNDOTBS1）
session 1正在执行如下操作：
conn test/test
create table test (name varchar(2));
insert into test values ('zhangsan');
commit;
update test set name='lisi' where name='zhangsan';
此时未提交

session 2开始切换undo表空间操作
（1）确认当前使用的undo表空间
SQL>  show parameter undo_tablespace
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace                      string      UNDOTBS2

（2）查看当前undo表空间的所有SELMENT均为ONLINE状态
SQL> select status,segment_name from dba_rollback_segs where status not in ('OFFLINE') and tablespace_name='UNDOTBS2';
STATUS           SEGMENT_NAME
---------------- ------------------------------
ONLINE           _SYSSMU11_3423735372$
ONLINE           _SYSSMU12_567660877$
ONLINE           _SYSSMU13_100563780$
ONLINE           _SYSSMU14_1447748955$
ONLINE           _SYSSMU15_478708454$
ONLINE           _SYSSMU16_3309423900$
ONLINE           _SYSSMU17_525951688$
ONLINE           _SYSSMU18_130984470$
ONLINE           _SYSSMU19_3964826557$
ONLINE           _SYSSMU20_994913344$
10 rows selected.

（3）创建新的undo表空间UNDOTBS1
SQL> create undo tablespace UNDOTBS1 datafile '/u01/app/oracle/oradata/orcl/undotbs01.dbf' size 10M;
Tablespace created.

（4）设置UNDOTBS1为默认undo表空间
SQL> alter system set undo_tablespace = undotbs1 scope=both;
System altered.

（5）此时检查UNDOTBS2中任然有一个SEGMENT处于ONLINE状态
SQL> select tablespace_name , status , count(*) from dba_rollback_segs group by tablespace_name , status;
TABLESPACE_NAME                STATUS             COUNT(*)
------------------------------ ---------------- ----------
UNDOTBS1                       ONLINE                   10
SYSTEM                         ONLINE                    1
UNDOTBS2                       OFFLINE                   9
UNDOTBS2                       ONLINE                    1

SQL> select status,segment_name from dba_rollback_segs where status not in ('OFFLINE') and tablespace_name='UNDOTBS2';
STATUS           SEGMENT_NAME
---------------- ------------------------------
ONLINE           _SYSSMU15_478708454$

故此时不能盲目删除UNDOTBS2表空间，否则将报错或导致数据不一致；甚至会导致数据库故障（或许~大概~有可能。。。）
此时需等待UNDOTBS2变为OFFLINE后才可执行删除该表空间的操作。
等待ing...

（6）此时回到session 1执行commit;，再回到session 2查看UNDOTBS2的状态变为OFFLINE
SQL> select status,segment_name from dba_rollback_segs where status not in ('OFFLINE') and tablespace_name='UNDOTBS2';
no rows selected

SQL> select tablespace_name , status , count(*) from dba_rollback_segs group by tablespace_name , status;
TABLESPACE_NAME                STATUS             COUNT(*)
------------------------------ ---------------- ----------
UNDOTBS1                       ONLINE                   10
SYSTEM                         ONLINE                    1
UNDOTBS2                       OFFLINE                  10

（7）此时删除UNDOTBS2即可
SQL> Drop tablespace UNDOTBS2 including contents and datafiles;
Tablespace dropped.

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

undo_tablespace                      string      UNDOTBS1

（8）至此，undo表空间由UNDOTBS2成功切换为UNDOTBS1

参考文章：IF: How to Switch to a New Undo Tablespace (文档 ID 1951695.1)

转自：http://blog.csdn.net/lk_db/article/details/51219266