oracle切换undo表空间(二)
oradata\orcl\UNDOTBS01.DBF
另一个程序正在使用此文件，进程无法访问。
将oracle的服务关闭后，可以删除。
重新切换，这次先offline，再drop。
SQL> create undo tablespace undotbs1 datafile 'E:\oracle\product\oradata\orcl\undotbs01.dbf' size 10M autoextend on next 10M maxsize 500M; www.2cto.com
Tablespace created
SQL> alter system set undo_tablespace='undotbs1' scope=both;
System altered
SQL> select tablespace_name,segment_name,status from dba_rollback_segs;
TABLESPACE_NAME SEGMENT_NAME STATUS
------------------------------ ------------------------------ ----------------
SYSTEM SYSTEM ONLINE
UNDOTBS1 _SYSSMU1$ ONLINE
UNDOTBS1 _SYSSMU2$ ONLINE
UNDOTBS1 _SYSSMU3$ ONLINE
UNDOTBS1 _SYSSMU4$ ONLINE
UNDOTBS1 _SYSSMU5$ ONLINE
UNDOTBS1 _SYSSMU6$ ONLINE
UNDOTBS1 _SYSSMU7$ ONLINE
UNDOTBS1 _SYSSMU8$ ONLINE
UNDOTBS1 _SYSSMU9$ ONLINE
UNDOTBS1 _SYSSMU10$ ONLINE
UNDOTBS2 _SYSSMU11$ OFFLINE
UNDOTBS2 _SYSSMU12$ OFFLINE
UNDOTBS2 _SYSSMU13$ OFFLINE
UNDOTBS2 _SYSSMU14$ OFFLINE
UNDOTBS2 _SYSSMU15$ OFFLINE
UNDOTBS2 _SYSSMU16$ OFFLINE
UNDOTBS2 _SYSSMU17$ OFFLINE
UNDOTBS2 _SYSSMU18$ OFFLINE
UNDOTBS2 _SYSSMU19$ OFFLINE
TABLESPACE_NAME SEGMENT_NAME STATUS
------------------------------ ------------------------------ ----------------
UNDOTBS2 _SYSSMU20$ OFFLINE
21 rows selected
SQL> alter tablespace undotbs2 offline;
Tablespace altered www.2cto.com
SQL> drop tablespace undotbs2 including contents and datafiles;
Tablespace dropped
SQL> select tablespace_name,segment_name,status from dba_rollback_segs;
TABLESPACE_NAME SEGMENT_NAME STATUS
------------------------------ ------------------------------ ----------------
SYSTEM SYSTEM ONLINE
UNDOTBS1 _SYSSMU1$ ONLINE
UNDOTBS1 _SYSSMU2$ ONLINE
UNDOTBS1 _SYSSMU3$ ONLINE
UNDOTBS1 _SYSSMU4$ ONLINE
UNDOTBS1 _SYSSMU5$ ONLINE
UNDOTBS1 _SYSSMU6$ ONLINE
UNDOTBS1 _SYSSMU7$ ONLINE
UNDOTBS1 _SYSSMU8$ ONLINE
UNDOTBS1 _SYSSMU9$ ONLINE
UNDOTBS1 _SYSSMU10$ 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 undotbs1
--EOF
原文链接：http://blog.yafeishi.net/2012/09/change-undo-tbs.
html