由于经常使用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:sqlbs_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>