收縮表消除表中的空塊

收縮一張表時，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