Oracle-10g-shrink-收缩表、索引原理与机制.docVIP

Oracle 10g shrink 收缩表、索引原理和机制http://www.D 从10g开始，oracle开始提供Shrink的命令，假如我们的表空间中支持自动段空间管理 (ASSM),就可以使用这个特性缩小段，即降低HWM。这里需要强调一点，10g的这个新特性，仅对ASSM表空间有效，否则会报 ORA-10635: Invalid segment or tablespace type。 在这里，我们来讨论如和对一个ASSM的segment回收浪费的空间。 同样，我们用系统视图all_objects来在tablespace ASSM上创建测试表my_objects，这一小节的内容，实验环境为oracle： SQL select * from v$version; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release .0 - Prod PL/SQL Release .0 - Production CORE .0 Production TNS for 32-bit Windows: Version .0 - Production NLSRTL Version .0 – Production SQL select TABLESPACE_NAME,BLOCK_SIZE,EXTENT_MANAGEMENT, 2 ALLOCATION_TYPE, SEGMENT_SPACE_MANAGEMENT 3 from dba_tablespaces where TABLESPACE_NAME = ASSM; TABLESPACE_NAME BLOCK_SIZE EXTENT_MANAGEMENT ALLOCATION_TYPE SEGMENT_SPACE_MANAGEMENT ---------------- ---------- ----------------- --------------- ------------------------ ASSM 8192 LOCAL UNIFORM AUTO SQL create table my_objects tablespace assm 2 as select * from all_objects; Table created 然后我们随机地从table MY_OBJECTS中删除一部分数据： SQL select count(*) from my_objects; COUNT(*) ---------- 47828 SQL delete from my_objects where object_name like %C%; 16950 rows deleted SQL delete from my_objects where object_name like %U%; 4503 rows deleted SQL delete from my_objects where object_name like %A%; 6739 rows deleted 现在我们使用show_space和show_space_assm来看看my_objects的数据存储状况： SQL exec show_space(MY_OBJECTS,DLINGER); Total Blocks............................680 Total Bytes.............................5570560 Unused Blocks...........................1 Unused Bytes............................8192 Last Used Ext FileId....................6 Last Used Ext BlockId...................793 Last Used Block.........................4 PL/SQL 过程已成功完成。 SQL exec show_space_assm(MY_OBJECTS,DLINGER); free space 0-25% Blocks:................0 free space 25-50% Blocks:...............205 free space 50-75% Blocks:...............180 fr