1、在创建新的undo tablesapce “undotbs2”后，删除旧的undo tablespace
15:12:49 SYS@ prod>ALTER tablespace undotbs1 offline immediate;
Tablespace altered.
Elapsed: 00:00:00.15
报以下错误：
15:12:59 SYS@ prod>drop tablespace undotbs1 including contents and datafiles;
drop tablespace undotbs1 including contents and datafiles
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU1_3780397527$' found, terminate dropping tablespace
Elapsed: 00:00:00.05
2、通过spfile生成pfile
15:13:08 SYS@ prod>create pfile from spfile;
File created.
Elapsed: 00:00:00.11
15:14:12 SYS@ prod>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
在initprod.ora 文件加入以下隐含参数：
_offline_rollback_segments=(_SYSSMU10_3550978943$,_SYSSMU9_1424341975$,_SYSSMU8_2012382730$,_SYSSMU7_3286610060$,_SYSSMU6_2443381498$,_SYSSMU5_1527469038$,_SYSSMU4_1152005954$,_SYSSMU3_2097677531$,_SYSSMU2_2232571081$,_SYSSMU1_3780397527$)
3、重新启动database，并删除旧的undo tablespace
[oracle@rh6 dbs]$ sqlplus '/as sysdba'
SQL*Plus: Release 11.2.0.1.0 Production on Thu Jun 26 16:32:49 2014
Copyright (c) 1982, 2009, Oracle.? All rights reserved.
Connected to an idle instance.
16:32:49 SYS@ prod>create spfile from pfile;
File created.
Elapsed: 00:00:00.04
16:32:52 SYS@ prod>startup
ORACLE instance started.
Total System Global Area? 835104768 bytes
Fixed Size? ? ? ? ? ? ? ? ? 2217952 bytes
Variable Size? ? ? ? ? ? 775948320 bytes
Database Buffers? ? ? ? ? 54525952 bytes
Redo Buffers? ? ? ? ? ? ? ? 2412544 bytes
Database mounted.
Database opened.
16:33:06 SYS@ prod>drop tablespace undotbs1 including contents and datafiles;
Tablespace dropped.
Elapsed: 00:00:00.59
16:33:17 SYS@ prod>shutdown immediate；
在pfile 删除_offline_rollback_segments参数；
16:33:56 SYS@ prod>create spfile from pfile;
File created.
Elapsed: 00:00:00.05
16:33:59 SYS@ prod>startup
ORACLE instance started.
Total System Global Area? 835104768 bytes
Fixed Size? ? ? ? ? ? ? ? ? 2217952 bytes
Variable Size? ? ? ? ? ? 775948320 bytes
Database Buffers? ? ? ? ? 54525952 bytes
Redo Buffers? ? ? ? ? ? ? ? 2412544 bytes
Database mounted.
Database opened.
?
16:34:17 SYS@ prod>select count(*) from scott.emp;
?
? COUNT(*)
----------
? ? ? ? 14
?
Elapsed: 00:00:00.05
16:34:20 SYS@ prod>select tablespace_name,status from dba_tablespaces;
?
TABLESPACE_NAME? ? ? ? ? ? ? ? STATUS
------------------------------ ---------
SYSTEM? ? ? ? ? ? ? ? ? ? ? ? ONLINE
SYSAUX? ? ? ? ? ? ? ? ? ? ? ? ONLINE
TEMP? ? ? ? ? ? ? ? ? ? ? ? ? ONLINE
USERS? ? ? ? ? ? ? ? ? ? ? ? ? ONLINE
UNDOTBS2? ? ? ? ? ? ? ? ? ? ? ONLINE
EXAMPLE? ? ? ? ? ? ? ? ? ? ? ? ONLINE
TBS1? ? ? ? ? ? ? ? ? ? ? ? ? ONLINE
7 rows selected.
Elapsed: 00:00:00.06
16:34:28 SYS@ prod>
@至此，undo tablespace 被正常删除！