| UNDO表空间的切换 | DBA之路：OoNiceDream |

UNDO表空间的切换

作者：OoNiceDream【转载时请务必以超链接形式标明文章原始出处和作者信息】
链接：http://www.dbaroad.me/archives/2008/11/switch-undo-tablespace.html

UNDO表空间切换，属于日常维护的常用的操作，比如进行数据加载时，常会切换一个更大的UNDO表空间。简单测试一把：

SQL> create undo tablespace undotbs2 datafile
  2  'D:\oracle\oradata\sky\undotbs02.dbf' SIZE 40m autoextend off;
 
Tablespace created.
 
SQL> show parameter undo
 
NAME                           TYPE        VALUE
------------------------------ ----------- ---------------------
undo_management                string      AUTO
undo_retention                 integer     10800
undo_suppress_errors           boolean     FALSE
undo_tablespace                string      UNDOTBS1
 
SQL> alter system set undo_tablespace=UNDOTBS2 scope=both;
 
System altered.
 
SQL> show parameter undo
 
NAME                           TYPE        VALUE
------------------------------ ----------- ---------------------
undo_management                string      AUTO
undo_retention                 integer     10800
undo_suppress_errors           boolean     FALSE
undo_tablespace                string      UNDOTBS2
 
SQL> select segment_name ,tablespace_name ,segment_id from dba_rollback_segs;
 
SEGMENT_NAME                   TABLESPACE_NAME                SEGMENT_ID
------------------------------ ------------------------------ ----------
SYSTEM                         SYSTEM                                  0
_SYSSMU1$                      UNDOTBS1                                1
_SYSSMU2$                      UNDOTBS1                                2
_SYSSMU3$                      UNDOTBS1                                3
_SYSSMU4$                      UNDOTBS1                                4
_SYSSMU5$                      UNDOTBS1                                5
_SYSSMU6$                      UNDOTBS1                                6
_SYSSMU7$                      UNDOTBS1                                7
_SYSSMU8$                      UNDOTBS1                                8
_SYSSMU9$                      UNDOTBS1                                9
_SYSSMU10$                     UNDOTBS1                               10
 
SEGMENT_NAME                   TABLESPACE_NAME                SEGMENT_ID
------------------------------ ------------------------------ ----------
_SYSSMU11$                     UNDOTBS2                               11
_SYSSMU12$                     UNDOTBS2                               12
_SYSSMU13$                     UNDOTBS2                               13
_SYSSMU14$                     UNDOTBS2                               14
_SYSSMU15$                     UNDOTBS2                               15
_SYSSMU16$                     UNDOTBS2                               16
_SYSSMU17$                     UNDOTBS2                               17
_SYSSMU18$                     UNDOTBS2                               18
_SYSSMU19$                     UNDOTBS2                               19
_SYSSMU20$                     UNDOTBS2                               20
 
21 rows selected.
 
SQL> select usn,status,xacts from v$rollstat;
 
       USN STATUS               XACTS
---------- --------------- ----------
         0 ONLINE                   0
        10 PENDING OFFLINE          1
        11 ONLINE                   0
        12 ONLINE                   0
        13 ONLINE                   0
        14 ONLINE                   0
        15 ONLINE                   0
        16 ONLINE                   0
        17 ONLINE                   0
        18 ONLINE                   0
        19 ONLINE                   1
 
       USN STATUS               XACTS
---------- --------------- ----------
        20 ONLINE                   0
 
12 rows selected.

发现原UNDOTBS1的回滚段处于PENDING OFFLINE状态，并有一个事物存在
如果要drop UNDOTBS1，需要等到事务完成后才行。

SQL> select usn,status,xacts from v$rollstat;
 
       USN STATUS               XACTS
---------- --------------- ----------
         0 ONLINE                   0
        10 PENDING OFFLINE          0　--事物结束
        11 ONLINE                   0
        12 ONLINE                   0
        13 ONLINE                   0
        14 ONLINE                   0
        15 ONLINE                   0
        16 ONLINE                   0
        17 ONLINE                   0
        18 ONLINE                   0
        19 ONLINE                   1
 
       USN STATUS               XACTS
---------- --------------- ----------
        20 ONLINE                   0
 
12 rows selected.
 
SQL> select usn,status,xacts from v$rollstat;
 
       USN STATUS               XACTS
---------- --------------- ----------
         0 ONLINE                   0
        11 ONLINE                   0
        12 ONLINE                   0
        13 ONLINE                   0
        14 ONLINE                   0
        15 ONLINE                   0
        16 ONLINE                   0
        17 ONLINE                   0
        18 ONLINE                   0
        19 ONLINE                   1
        20 ONLINE                   0
 
11 rows selected.
 
SQL> drop tablespace undotbs1 including contents and datafiles;
 
Tablespace dropped.
 
SQL>