收縮一張表時(shí)，Oracle會以占用最小存儲空間的方式來重新組織數(shù)據(jù)塊。Delete操作通常不會降低高水位線，當(dāng)做全表掃描時(shí)oracle會掃描高水位線下的塊，一個(gè)表的數(shù)據(jù)可能不多但執(zhí)行時(shí)間卻很長，通常需要注意高水位下會有很多空閑的塊存在。通常可通過對表進(jìn)行shrink或move釋放被占用的空間。

成都創(chuàng)新互聯(lián)堅(jiān)持“要么做到，要么別承諾”的工作理念，服務(wù)領(lǐng)域包括：成都網(wǎng)站設(shè)計(jì)、成都做網(wǎng)站、企業(yè)官網(wǎng)、英文網(wǎng)站、手機(jī)端網(wǎng)站、網(wǎng)站推廣等服務(wù)，滿足客戶于互聯(lián)網(wǎng)時(shí)代的浦城網(wǎng)站設(shè)計(jì)、移動(dòng)媒體設(shè)計(jì)的需求，幫助企業(yè)找到有效的互聯(lián)網(wǎng)解決方案。努力成為您成熟可靠的網(wǎng)絡(luò)建設(shè)合作伙伴！

shrink表需要兩步：1.啟用行遷移；2.收縮表；

啟用行遷移
SQL> alter table t enable row movement;
收縮表
SQL> alter table t shrink space;
收縮表及相關(guān)的索引
SQL> alter table t shrink space cascade;
僅收縮表，不調(diào)高水位
SQL> alter table t shrink space compact;

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

可使用tom的show_space進(jìn)行檢查是否有空塊存在：

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

新聞名稱：收縮表消除表中的空塊
鏈接地址：http://chinadenli.net/article36/pigpsg.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供標(biāo)簽優(yōu)化、網(wǎng)站內(nèi)鏈、網(wǎng)站制作、網(wǎng)站改版、建站公司、用戶體驗(yàn)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián)