oracle切换undo表空间
1.新建undo表空间
SQL> create undo tablespace undotbs2 datafile 'E:\oracle\product\oradata\orcl\un
dotbs02.dbf' size 10M autoextend on next 10M maxsize 500M;
2.由于undo_tablespace是个动态参数，所以任何时候都可以set。
SQL> alter system set undo_tablespace='undotbs2' scope=both;
系统已更改。 www.2cto.com
3.查看原来的undo表空间中的undo segment是否都为offline
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 www.2cto.com
原来undotbs1的undo_segment 都为offline，则可以删除undotbs1.
SQL> drop tablespace undotbs1 including contents and datafiles;
表空间已删除。
再次查看：
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 www.2cto.com
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- -----------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string undotbs2
由于没有offline undotbs1，所以物理删除数据文件的时候报错：
E:\oracle\product\oradata\orcl>del undotbs01.dbf
E:\oracle\product\