由于经常使用awrload功能，把客户数据库的awr信息加载到我的DB中，发现自己的数据库undo表空间比较大，

的想办法搜身。

D:oracleoradatadb11g>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on 星期四 1月 16 14:06:55 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>@d:sql bs_info.sql

Name      Type          Extent Man Size (M)        Used (M)           Used %     

-------- -----------   --------------------    ------------       ----------
SYSAUX   PERMANENT     LOCAL                    8208/8640               95.00        

UNDOTBS1 UNDO          LOCAL                    2580/4350               59.30   

UNDO表空间大小为4350M。

SQL> alter database datafile 'D:ORACLEORADATADB11GUNDOTBS01.DBF' resize 2G;
alter database datafile 'D:ORACLEORADATADB11GUNDOTBS01.DBF' resize 2G
*
第 1 行出现错误:
ORA-03297: 文件包含在请求的 RESIZE 值以外使用的数据

SQL> create undo tablespace undotbs2 datafile 'd:oracleoradatadb11gundotbs2.dbf' size 128M autoextend on next 128m maxsize 4g;

表空间已创建。

SQL> alter system set undo_tablespace=undotbs2;

系统已更改。

SQL> drop tablespace undotbs1 including contents and datafiles;
drop tablespace undotbs1 including contents and datafiles
*
第 1 行出现错误:
ORA-30013: 还原表空间 'UNDOTBS1' 当前正在使用中

SQL> select name from undo$;

NAME
------------------------------------------------------------
SYSTEM
_SYSSMU10_1721075589$
_SYSSMU11_2237073343$
_SYSSMU12_3097604597$
_SYSSMU13_2730169332$
_SYSSMU14_2651596459$
_SYSSMU15_3184076291$
_SYSSMU16_342329819$
_SYSSMU17_4203399684$
_SYSSMU18_1328849355$
_SYSSMU19_2255122368$
_SYSSMU1_1151437724$
_SYSSMU20_1374499095$
_SYSSMU21_2629470354$
_SYSSMU2_4024591198$
_SYSSMU3_855526156$
_SYSSMU4_3779374695$
_SYSSMU5_3429037733$
_SYSSMU6_393561114$
_SYSSMU7_684791253$
_SYSSMU8_1373814157$
_SYSSMU9_1261004971$

已选择22行。

SQL>  drop rollback segment "_SYSSMU1_1151437724$";
 drop rollback segment "_SYSSMU1_1151437724$"
*
第 1 行出现错误:
ORA-30025: 不允许删除段 '_SYSSMU1_1151437724$' (在还原表空间中)

SQL> alter system set "_smu_debug_mode"=32;

系统已更改。

SQL>  drop rollback segment "_SYSSMU1_1151437724$";
 drop rollback segment "_SYSSMU1_1151437724$"
*
第 1 行出现错误:
ORA-30025: 不允许删除段 '_SYSSMU1_1151437724$' (在还原表空间中)

SQL>  drop rollback segment "_SYSSMU2_4024591198$";
 drop rollback segment "_SYSSMU2_4024591198$"
*
第 1 行出现错误:
ORA-30025: 不允许删除段 '_SYSSMU2_4024591198$' (在还原表空间中)

试试重启数据库

SQL> startup force;
ORACLE 例程已经启动。

Total System Global Area 3407261696 bytes
Fixed Size                  2259960 bytes
Variable Size            2533360648 bytes
Database Buffers          855638016 bytes
Redo Buffers               16003072 bytes
数据库装载完毕。
数据库已经打开。

SQL> drop tablespace undotbs1 including contents and datafiles;
drop tablespace undotbs1 including contents and datafiles
*
第 1 行出现错误:
ORA-01548: 已找到活动回退段 '_SYSSMU5_3429037733$', 终止删除表空间

SQL>  select segment_name,status from dba_rollback_segs;

SEGMENT_NAME                   STATUS
------------------------------ ----------------
SYSTEM                         ONLINE
_SYSSMU5_3429037733$           PARTLY AVAILABLE
_SYSSMU6_393561114$            PARTLY AVAILABLE
_SYSSMU7_684791253$            OFFLINE
_SYSSMU8_1373814157$           OFFLINE
_SYSSMU9_1261004971$           OFFLINE
_SYSSMU10_1721075589$          OFFLINE
_SYSSMU11_2237073343$          OFFLINE
_SYSSMU12_3097604597$          ONLINE
_SYSSMU13_2730169332$          ONLINE
_SYSSMU14_2651596459$          ONLINE
_SYSSMU15_3184076291$          ONLINE
_SYSSMU16_342329819$           ONLINE
_SYSSMU17_4203399684$          ONLINE
_SYSSMU18_1328849355$          ONLINE
_SYSSMU19_2255122368$          ONLINE
_SYSSMU20_1374499095$          ONLINE
_SYSSMU21_2629470354$          ONLINE

已选择18行。

SQL> alter rollback segment "_SYSSMU5_3429037733$" offline;

回退段已变更。

SQL> alter rollback segment "_SYSSMU6_393561114$" offline;

回退段已变更。

SQL>  select segment_name,status from dba_rollback_segs;

SEGMENT_NAME                   STATUS
------------------------------ ----------------
SYSTEM                         ONLINE
_SYSSMU5_3429037733$           PARTLY AVAILABLE
_SYSSMU6_393561114$            PARTLY AVAILABLE
_SYSSMU7_684791253$            OFFLINE
_SYSSMU8_1373814157$           OFFLINE
_SYSSMU9_1261004971$           OFFLINE
_SYSSMU10_1721075589$          OFFLINE
_SYSSMU11_2237073343$          OFFLINE
_SYSSMU12_3097604597$          ONLINE
_SYSSMU13_2730169332$          ONLINE
_SYSSMU14_2651596459$          ONLINE
_SYSSMU15_3184076291$          ONLINE
_SYSSMU16_342329819$           ONLINE
_SYSSMU17_4203399684$          ONLINE
_SYSSMU18_1328849355$          ONLINE
_SYSSMU19_2255122368$          ONLINE
_SYSSMU20_1374499095$          ONLINE
_SYSSMU21_2629470354$          ONLINE

已选择18行。

SQL> drop rollback segment "_SYSSMU5_3429037733$";
drop rollback segment "_SYSSMU5_3429037733$"
*
第 1 行出现错误:
ORA-30025: 不允许删除段 '_SYSSMU5_3429037733$' (在还原表空间中)

SQL> alter system set "_smu_debug_mode"=4;

系统已更改。

SQL> drop rollback segment "_SYSSMU5_3429037733$";
drop rollback segment "_SYSSMU5_3429037733$"
*
第 1 行出现错误:
ORA-01545: 指定的回退段 '_SYSSMU5_3429037733$' 不可用

SQL>  select segment_name,status from dba_rollback_segs;

SEGMENT_NAME                   STATUS
------------------------------ ----------------
SYSTEM                         ONLINE
_SYSSMU5_3429037733$           PARTLY AVAILABLE
_SYSSMU6_393561114$            PARTLY AVAILABLE
_SYSSMU7_684791253$            OFFLINE
_SYSSMU8_1373814157$           OFFLINE
_SYSSMU9_1261004971$           OFFLINE
_SYSSMU10_1721075589$          OFFLINE
_SYSSMU11_2237073343$          OFFLINE
_SYSSMU12_3097604597$          ONLINE
_SYSSMU13_2730169332$          ONLINE
_SYSSMU14_2651596459$          ONLINE
_SYSSMU15_3184076291$          ONLINE
_SYSSMU16_342329819$           ONLINE
_SYSSMU17_4203399684$          ONLINE
_SYSSMU18_1328849355$          ONLINE
_SYSSMU19_2255122368$          ONLINE
_SYSSMU20_1374499095$          ONLINE
_SYSSMU21_2629470354$          ONLINE

已选择18行。

SQL> alter  rollback segment "_SYSSMU5_3429037733$" offline;
alter  rollback segment "_SYSSMU5_3429037733$" offline
*
第 1 行出现错误:
ORA-01598: 回退段 '_SYSSMU5_3429037733$' 未联机

SQL> drop rollback segment "_SYSSMU5_3429037733$";
drop rollback segment "_SYSSMU5_3429037733$"
*
第 1 行出现错误:
ORA-01545: 指定的回退段 '_SYSSMU5_3429037733$' 不可用

SQL> drop tablespace undotbs1 including contents and datafiles;
drop tablespace undotbs1 including contents and datafiles
*
第 1 行出现错误:
ORA-01548: 已找到活动回退段 '_SYSSMU5_3429037733$', 终止删除表空间

SQL> alter system set "_offline_rollback_segments"='_SYSSMU5_3429037733$','_SYSSMU6_393561114$' scope=spfile;

系统已更改。

SQL> startup force
ORACLE 例程已经启动。

Total System Global Area 3407261696 bytes
Fixed Size                  2259960 bytes
Variable Size            2533360648 bytes
Database Buffers          855638016 bytes
Redo Buffers               16003072 bytes
数据库装载完毕。
数据库已经打开。
SQL>  select segment_name,status from dba_rollback_segs;

SEGMENT_NAME                   STATUS
------------------------------ ----------------
SYSTEM                         ONLINE
_SYSSMU5_3429037733$           NEEDS RECOVERY
_SYSSMU6_393561114$            NEEDS RECOVERY
_SYSSMU7_684791253$            OFFLINE
_SYSSMU8_1373814157$           OFFLINE
_SYSSMU9_1261004971$           OFFLINE
_SYSSMU10_1721075589$          OFFLINE
_SYSSMU11_2237073343$          OFFLINE
_SYSSMU12_3097604597$          ONLINE
_SYSSMU13_2730169332$          ONLINE
_SYSSMU14_2651596459$          ONLINE
_SYSSMU15_3184076291$          ONLINE
_SYSSMU16_342329819$           ONLINE
_SYSSMU17_4203399684$          ONLINE
_SYSSMU18_1328849355$          ONLINE
_SYSSMU19_2255122368$          ONLINE
_SYSSMU20_1374499095$          ONLINE
_SYSSMU21_2629470354$          ONLINE

已选择18行。

SQL> drop tablespace undotbs1 including contents and datafiles;

表空间已删除。

SQL>  select segment_name,status from dba_rollback_segs;

SEGMENT_NAME                   STATUS
------------------------------ ----------------
SYSTEM                         ONLINE
_SYSSMU12_3097604597$          ONLINE
_SYSSMU13_2730169332$          ONLINE
_SYSSMU14_2651596459$          ONLINE
_SYSSMU15_3184076291$          ONLINE
_SYSSMU16_342329819$           ONLINE
_SYSSMU17_4203399684$          ONLINE
_SYSSMU18_1328849355$          ONLINE
_SYSSMU19_2255122368$          ONLINE
_SYSSMU20_1374499095$          ONLINE
_SYSSMU21_2629470354$          ONLINE

已选择11行。

SQL> alter system reset "_offline_rollback_segments";

系统已更改。

SQL> alter system reset "_smu_debug_mode";

系统已更改。

SQL> alter tablespace undotbs2 rename to undotbs1;

表空间已更改。

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS2
SQL> alter system set undo_tablespace=undotbs1;

系统已更改。

SQL>