一般来说我们仍推荐使用重建UNDO TABLESPACE的方法, 而不推荐收缩UNDO表空间的数据文件,具体可以参考文档:
How to Shrink the datafile of Undo Tablespace [ID 268870.1]
下面提供一个例子 使用 smudebug_mode 在AUM自动UNDO管理模式下 可以手动操作ROLLBACK SEGMENT, DROP ROLLBACK SEGMENT并下降UNDO DATAFILE的 HWM:
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL> select segment_name,(block_id+blocks-1)*8/1024 "MB" from dba_extents where tablespace_name='UNDOTBS1' order by BLOCK_ID
2 ;
SEGMENT_NAME MB
------------------------------ ----------
_SYSSMU1_1240252155$ 1.0546875
_SYSSMU3_4004931649$ 1.1171875
_SYSSMU2_111974964$ 1.1796875
_SYSSMU11_658781855$ 1.2421875
_SYSSMU3_4004931649$ 1.3046875
_SYSSMU8_3612859353$ 1.3671875
_SYSSMU4_1126976075$ 1.4296875
_SYSSMU12_2501004885$ 1.4921875
_SYSSMU5_4011504098$ 1.5546875
_SYSSMU19_2180579991$ 1.6171875
_SYSSMU6_3654194381$ 1.6796875
_SYSSMU4_1126976075$ 1.7421875
_SYSSMU7_4222772309$ 1.80