本文主要向大家介绍了Oracle数据库之收缩表消除表中的空块，通过具体的内容向大家展现，希望对大家学习Oracle数据库有所帮助。

收缩一张表时，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;
/

本文由职坐标整理并发布，希望对同学们学习Oracle有所帮助，更多内容请关注职坐标数据库Oracle数据库频道！