收缩表消除表中的空块

收缩一张表时，Oracle会以占用最小存储空间的方式来重新组织数据块。Delete操作通常不会降低高水位线，当做全表扫描时oracle会扫描高水位线下的块，一个表的数据可能不多但执行时间却很长，通常需要注意高水位下会有很多空闲的块存在。通常可通过对表进行shrink或move释放被占用的空间。

shrink表需要两步：1.启用行迁移；2.收缩表；

启用行迁移 SQL> alter table t enable row movement; 收缩表 SQL> alter table t shrink space; 收缩表及相关的索引 SQL> alter table t shrink space cascade; 仅收缩表，不调高水位 SQL> alter table t shrink space compact;

move表需要两步：1.move表；2.重建相关索引 **SQL> alter table t move tablespace users; SQL> alter index idx_t on t(id) rebuild online tablespace idx_tbs; **

可使用tom的show_space进行检查是否有空块存在：

create or replace procedure show_space(p_segname_1 in varchar2, p_owner_1 in varchar2 default user, p_type_1 in varchar2 default 'TABLE', p_space in varchar2 default 'AUTO', p_analyzed in varchar2 default 'Y') as p_segname varchar2(100); p_type varchar2(10); p_owner varchar2(30); l_unformatted_blocks number; l_unformatted_bytes number; l_fs1_blocks number; l_fs1_bytes number; l_fs2_blocks number; l_fs2_bytes number; l_fs3_blocks number; l_fs3_bytes number; l_fs4_blocks number; l_fs4_bytes number; l_full_blocks number; l_full_bytes number; l_free_blks number; l_total_blocks number; l_total_bytes number; l_unused_blocks number; l_unused_bytes number; l_LastUsedExtFileId number; l_LastUsedExtBlockId number; l_LAST_USED_BLOCK number; procedure p(p_label in varchar2, p_num in number) is begin dbms_output.put_line(rpad(p_label, 40, '.') || p_num); end; begin p_segname := upper(p_segname_1); -- rainy changed p_owner := upper(p_owner_1); p_type := p_type_1; if (p_type_1 = 'i' or p_type_1 = 'I') then --rainy changed p_type := 'INDEX'; end if; if (p_type_1 = 't' or p_type_1 = 'T') then --rainy changed p_type := 'TABLE'; end if; if (p_type_1 = 'c' or p_type_1 = 'C') then --rainy changed p_type := 'CLUSTER'; end if; dbms_space.unused_space(segment_owner => p_owner, segment_name => p_segname, segment_type => p_type, total_blocks => l_total_blocks, total_bytes => l_total_bytes, unused_blocks => l_unused_blocks, unused_bytes => l_unused_bytes, LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId, LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId, LAST_USED_BLOCK => l_LAST_USED_BLOCK); if p_space = 'MANUAL' or (p_space <> 'auto' and p_space <> 'AUTO') then dbms_space.free_blocks(segment_owner => p_owner, segment_name => p_segname, segment_type => p_type, freelist_group_id => 0, free_blks => l_free_blks); p('Free Blocks', l_free_blks); end if; p('Total Blocks', l_total_blocks); p('Total Bytes', l_total_bytes); p('Unused Blocks', l_unused_blocks); p('Unused Bytes', l_unused_bytes); p('Last Used Ext FileId', l_LastUsedExtFileId); p('Last Used Ext BlockId', l_LastUsedExtBlockId); p('Last Used Block', l_LAST_USED_BLOCK); /*IF the segment is analyzed / if p_analyzed = 'Y' then dbms_space.space_usage(segment_owner => p_owner, segment_name => p_segname, segment_type => p_type, unformatted_blocks => l_unformatted_blocks, unformatted_bytes => l_unformatted_bytes, fs1_blocks => l_fs1_blocks, fs1_bytes => l_fs1_bytes, fs2_blocks => l_fs2_blocks, fs2_bytes => l_fs2_bytes, fs3_blocks => l_fs3_blocks, fs3_bytes => l_fs3_bytes, fs4_blocks => l_fs4_blocks, fs4_bytes => l_fs4_bytes, full_blocks => l_full_blocks, full_bytes => l_full_bytes); dbms_output.put_line(rpad(' ', 50, '')); dbms_output.put_line('The segment is analyzed'); p('0% -- 25% free space blocks', l_fs1_blocks); p('0% -- 25% free space bytes', l_fs1_bytes); p('25% -- 50% free space blocks', l_fs2_blocks); p('25% -- 50% free space bytes', l_fs2_bytes); p('50% -- 75% free space blocks', l_fs3_blocks); p('50% -- 75% free space bytes', l_fs3_bytes); p('75% -- 100% free space blocks', l_fs4_blocks); p('75% -- 100% free space bytes', l_fs4_bytes); p('Unused Blocks', l_unformatted_blocks); p('Unused Bytes', l_unformatted_bytes); p('Total Blocks', l_full_blocks); p('Total bytes', l_full_bytes); end if; end; /

用法参考： https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5350053031470