1.新建undo表空间

1
SQL> create undo tablespace undotbs2 datafile '/data/undotbs02.dbf' size 10M autoextend on next 10M ;

2.由于undo_tablespace是个动态参数，所以任何时候都可以set。

1
SQL> alter system set undo_tablespace='undotbs2' scope=both;

系统已更改。

3.查看原来的undo表空间中的undo segment是否都为offline

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
SQL> select tablespace_name,segment_name,status from dba_rollback_segs; 
TABLESPACE_NAME                SEGMENT_NAME                   STATUS 
------------------------------ ------------------------------ ---------------- 
SYSTEM                         SYSTEM                         ONLINE 
UNDOTBS1                       _SYSSMU1$                      OFFLINE 
UNDOTBS1                       _SYSSMU2$                      OFFLINE 
UNDOTBS1                       _SYSSMU3$                      OFFLINE 
UNDOTBS1                       _SYSSMU4$                      OFFLINE 
UNDOTBS1                       _SYSSMU5$                      OFFLINE 
UNDOTBS1                       _SYSSMU6$                      OFFLINE 
UNDOTBS1                       _SYSSMU7$                      OFFLINE 
UNDOTBS1                       _SYSSMU8$                      OFFLINE 
UNDOTBS1                       _SYSSMU9$                      OFFLINE 
UNDOTBS1                       _SYSSMU10$                     OFFLINE 
UNDOTBS2                       _SYSSMU11$                     ONLINE 
UNDOTBS2                       _SYSSMU12$                     ONLINE 
UNDOTBS2                       _SYSSMU13$                     ONLINE 
UNDOTBS2                       _SYSSMU14$                     ONLINE 
UNDOTBS2                       _SYSSMU15$                     ONLINE 
UNDOTBS2                       _SYSSMU16$                     ONLINE 
UNDOTBS2                       _SYSSMU17$                     ONLINE 
UNDOTBS2                       _SYSSMU18$                     ONLINE 
UNDOTBS2                       _SYSSMU19$                     ONLINE 
TABLESPACE_NAME                SEGMENT_NAME                   STATUS 
------------------------------ ------------------------------ ---------------- 
UNDOTBS2                       _SYSSMU20$                     ONLINE 
21 rows selected

原来undotbs1的undo_segment 都为offline，则可以删除undotbs1. 

1
2
SQL> alter tablespace undotbs1 offline;
SQL> drop tablespace undotbs1 including contents and datafiles;

表空间已删除。 再次查看： 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SQL> select tablespace_name,segment_name,status from dba_rollback_segs;
TABLESPACE_NAME                SEGMENT_NAME                   STATUS
------------------------------ ------------------------------ ----------------
SYSTEM                         SYSTEM                         ONLINE
UNDOTBS2                       _SYSSMU11$                     ONLINE
UNDOTBS2                       _SYSSMU12$                     ONLINE
UNDOTBS2                       _SYSSMU13$                     ONLINE
UNDOTBS2                       _SYSSMU14$                     ONLINE
UNDOTBS2                       _SYSSMU15$                     ONLINE
UNDOTBS2                       _SYSSMU16$                     ONLINE
UNDOTBS2                       _SYSSMU17$                     ONLINE
UNDOTBS2                       _SYSSMU18$                     ONLINE
UNDOTBS2                       _SYSSMU19$                     ONLINE
UNDOTBS2                       _SYSSMU20$                     ONLINE
11 rows selected  
SQL> show parameter undo
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      undotbs1
--EOF