oracle加undo+resize,undo表空间收缩问题！

一般来说我们仍推荐使用重建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.8046875

_SYSSMU13_840218468$ 1.8671875

_SYSSMU8_3612859353$ 1.9296875

ROL1 1.9921875

_SYSSMU9_3945653786$ 2.0546875

ROL1 2.1171875

_SYSSMU10_3271578125$ 2.1796875

ROL2 2.2421875

_SYSSMU14_2349236253$ 2.3046875

_SYSSMU15_1111071581$ 2.3671875

_SYSSMU33_827532347$ 2.4296875

_SYSSMU16_2343055295$ 2.4921875

ROL2 2.5546875

_SYSSMU18_1944602643$ 2.6171875

ROL3 2.6796875

_SYSSMU9_3945653786$ 2.7421875

_SYSSMU17_2053564160$ 2.8046875

_SYSSMU13_840218468$ 2.8671875

_SYSSMU20_799523511$ 2.9296875

_SYSSMU32_717097254$ 2.9921875

ROL3 3.0546875

_SYSSMU31_3126164870$ 3.1171875

_SYSSMU14_2349236253$ 3.1796875

_SYSSMU32_717097254$ 3.2421875

_SYSSMU5_4011504098$ 3.3046875

_SYSSMU33_827532347$ 3.3671875

_SYSSMU14_2349236253$ 3.4296875

_SYSSMU14_2349236253$ 3.4921875

_SYSSMU13_840218468$ 3.5546875

_SYSSMU1_1240252155$ 3.6171875

_SYSSMU14_2349236253$ 3.7421875

_SYSSMU10_3271578125$ 3.8046875

_SYSSMU27_3300739595$ 4.0546875

_SYSSMU12_2501004885$ 4.1171875

_SYSSMU14_2349236253$ 4.2421875

_SYSSMU11_658781855$ 4.3046875

_SYSSMU14_2349236253$ 4.3671875

_SYSSMU7_4222772309$ 4.5546875

_SYSSMU21_1329673384$ 4.6171875

_SYSSMU21_1329673384$ 4.6796875

_SYSSMU22_2337540263$ 4.7421875

_SYSSMU22_2337540263$ 4.8671875

_SYSSMU23_1882401082$ 4.9296875

_SYSSMU6_3654194381$ 5.1171875

_SYSSMU29_2315271841$ 5.2421875

_SYSSMU30_4027581112$ 5.3046875

_SYSSMU28_1041049599$ 5.3671875

_SYSSMU31_3126164870$ 5.4296875

_SYSSMU18_1944602643$ 5.4921875

_SYSSMU15_1111071581$ 5.6171875

_SYSSMU30_4027581112$ 5.6796875

_SYSSMU24_537967391$ 5.8046875

_SYSSMU2_111974964$ 5.9296875

_SYSSMU24_537967391$ 6.9921875

_SYSSMU26_668922729$ 7.9921875

_SYSSMU24_537967391$ 8.1171875

_SYSSMU25_4148647911$ 8.2421875

_SYSSMU19_2180579991$ 8.3046875

_SYSSMU26_668922729$ 8.4296875

_SYSSMU26_668922729$ 8.4921875

_SYSSMU27_3300739595$ 8.5546875

_SYSSMU28_1041049599$ 8.7421875

_SYSSMU29_2315271841$ 8.8671875

_SYSSMU30_4027581112$ 8.9921875

_SYSSMU31_3126164870$ 9.9921875

_SYSSMU12_2501004885$ 10.9921875

_SYSSMU8_3612859353$ 11.9921875

_SYSSMU27_3300739595$ 12.9921875

_SYSSMU33_827532347$ 13.9921875

_SYSSMU11_658781855$ 14.9921875

_SYSSMU15_1111071581$ 15.9921875

_SYSSMU5_4011504098$ 16.9921875

_SYSSMU9_3945653786$ 17.9921875

_SYSSMU7_4222772309$ 18.9921875

_SYSSMU22_2337540263$ 19.9921875

_SYSSMU20_799523511$ 20.9921875

_SYSSMU2_111974964$ 21.9921875

_SYSSMU25_4148647911$ 24.9921875

_SYSSMU16_2343055295$ 25.1171875

_SYSSMU25_4148647911$ 25.2421875

_SYSSMU3_4004931649$ 26.9921875

_SYSSMU10_3271578125$ 27.9921875

_SYSSMU1_1240252155$ 28.9921875

_SYSSMU10_3271578125$ 29.9921875

_SYSSMU7_4222772309$ 31.9921875

_SYSSMU6_3654194381$ 33.9921875

_SYSSMU29_2315271841$ 34.9921875

_SYSSMU21_1329673384$ 35.9921875

_SYSSMU17_2053564160$ 36.1796875

_SYSSMU16_2343055295$ 37.9921875

_SYSSMU23_1882401082$ 38.1796875

_SYSSMU5_4011504098$ 39.9921875

_SYSSMU9_3945653786$ 40.9921875

_SYSSMU17_2053564160$ 46.1171875

_SYSSMU2_111974964$ 47.9921875

_SYSSMU1_1240252155$ 48.9921875

_SYSSMU7_4222772309$ 49.9921875

_SYSSMU2_111974964$ 54.9921875

_SYSSMU9_3945653786$ 56.9921875

_SYSSMU3_4004931649$ 57.9921875

_SYSSMU6_3654194381$ 58.9921875

_SYSSMU5_4011504098$ 59.9921875

_SYSSMU4_1126976075$ 61.9921875

_SYSSMU4_1126976075$ 68.9921875

_SYSSMU8_3612859353$ 69.9921875

117 rows selected.

1* select file_name from dba_data_files where tablespace_name='UNDOTBS1'

SQL> /

FILE_NAME

------------------------------------------------------------

+DATA/proda/datafile/undotbs1.258.794669839

SQL> alter database datafile '+DATA/proda/datafile/undotbs1.258.794669839' resize 70M;

Database altered.

SQL> alter database datafile '+DATA/proda/datafile/undotbs1.258.794669839' resize 69M;

alter database datafile '+DATA/proda/datafile/undotbs1.258.794669839' resize 69M

*

ERROR at line 1:

ORA-03297: file contains used data beyond requested RESIZE value

SQL> alter system set "_smu_debug_mode"=4;

System altered.

SQL> alter rollback segment "_SYSSMU8_3612859353$" offline;

Rollback segment altered.

SQL> drop rollback segment "_SYSSMU8_3612859353$";

Rollback segment dropped.

SQL> select max(block_id+blocks-1)*8/1024 "MB" from dba_extents where tablespace_name='UNDOTBS1';

MB

----------

68.9921875

SQL> alter database datafile '+DATA/proda/datafile/undotbs1.258.794669839' resize 69M;

Database altered.如以上例子通过 "_smu_debug_mode"=4; 后 offline 、 drop rollback segment 实现对UNDO DATAFILE的RESIZE。

建议该类操作不要在高并发的情况下做， 一般找一个空闲的时间来操作。

*如果觉得本回复有意义，请点击本条回复右手边的Correct按钮，谢谢！*

如何在OTN中文技术论坛提一个问题?

论坛礼仪需知及学习oracle的方法论

Maclean Liu

Oracle Database Administrator

Oracle Certified 10g/11g Master

www.askmaclean.com