ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > Oracle表碎片整理实战：SHRINK还是MOVE？完整实验对比

Oracle表碎片整理实战：SHRINK还是MOVE？完整实验对比

原创 Oracle 作者：marui 时间：2026-06-26 14:49:44 0 删除 编辑

大家好，我是睿。


表碎片是Oracle DBA日常维护中经常遇到的问题。很多表在经过大量数据插入和删除后，高水位线一直居高不下，即使实际数据已经很少，全表扫描时还是会读很多无效块，导致性能变差。


最近我专门做了一个实验，用同一张表对比了 SHRINK 和 MOVE 两种缩表方式的实际效果，包括操作步骤、资源消耗、整理后的段大小和索引情况等。今天把整个过程完整分享出来，供大家参考。

## 一、表碎片是怎么产生的？


简单来说，当表频繁INSERT数据后，又执行大量DELETE操作时，高水位线只会上升，不会自动下降。DELETE只会把数据块标记为空闲，但高水位线位置不变。所以全表扫描时，Oracle仍然会扫描到原来的高水位线，产生大量无效逻辑读。


## 二、两种主流缩表方式


1. SHRINK方式（10g引入，支持在线操作）


```sql

必须先启用行移动

ALTER TABLE mary.test ENABLE ROW MOVEMENT;


第一步：压缩整理（不释放空间）

ALTER TABLE mary.test SHRINK SPACE COMPACT;


第二步：真正释放空间并处理索引

ALTER TABLE mary.test SHRINK SPACE CASCADE;

```



2. MOVE方式（效果最彻底）


```sql

ALTER TABLE mary.test MOVE;


MOVE完成后，所有相关索引都会失效，必须重建：

ALTER INDEX idx_test_id REBUILD ONLINE;

```


## 三、如何判断表是否存在碎片？



我常用下面这个SQL快速查看：



```sql

set linesize 500


set pagesize 300


col owner for a15


col table_name for a10


SELECT owner, table_name, num_rows,


       ROUND((blocks*8)/1024,2) "HIGH_WATER_MB",


       ROUND((num_rows*avg_row_len/1024/1024),2) "REAL_DATA_MB",


       ROUND((blocks*8 - num_rows*avg_row_len/1024 - blocks*8*0.1)/1024,2) "WASTE_MB"


FROM dba_tables


WHERE owner = 'MARY' AND table_name = 'TEST';




如果想看得更详细，可以使用 show_space 存储过程（适用于ASSM表空间）：




show_space完整存储过程


create or replace procedure show_space


( p_segname_1 in varchar2,


p_space in varchar2 default 'MANUAL',


p_type_1 in varchar2 default 'TABLE' ,


p_analyzed in varchar2 default 'N',


p_owner_1 in varchar2 default user)


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);


p_owner := upper(p_owner_1);


p_type := p_type_1;


if (p_type_1 = 'i' or p_type_1 = 'I') then


p_type := 'INDEX';


end if;


if (p_type_1 = 't' or p_type_1 = 'T') then


p_type := 'TABLE';


end if;


if (p_type_1 = 'c' or p_type_1 = 'C') then


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;


/

```




使用方法：


```sql

SET SERVEROUTPUT ON


EXEC show_space('TEST', 'AUTO', 'TABLE', 'Y', 'MARY');

```


## 四、完整实验过程




我新建了两张测试表 mary.test2026、mary.test2026_2，分别插入约500万行数据，然后DELETE全量数据，制造碎片，分别用两种方式处理并观察效果。


实验准备（制造碎片）：


# 创建测试表


```sql

CREATE TABLE mary.test2026 (id NUMBER primary key, name VARCHAR2(50), create_time DATE);

```


# 插入测试数据


```sql

BEGIN


  FOR i IN 1..5000000 LOOP


    INSERT INTO mary.test2026 VALUES (i, 'test'||i, SYSDATE);


  END LOOP;


  COMMIT;


END;


/

```


# 创建第二张测试表、插入数据、添加主键


```sql

create table mary.test2026_2 as select * from mary.test2026;


alter table mary.test2026_2 modify (id NUMBER constraint mary_pk primary key);

```


# 收集统计信息


```sql

execute dbms_stats.gather_table_stats ('MARY','TEST2026');


execute dbms_stats.gather_table_stats ('MARY','TEST2026_2');

```


# 查看两张测试表的段大小


```sql

col segment_name for a40


select segment_name,sum(bytes)/1024/1024 as "size_MB" from dba_segments where segment_name in ('TEST2026','TEST2026_2') and owner='MARY' group by segment_name;

```



SEGMENT_NAME                                size_MB


---------------------------------------- ----------


TEST2026                                        168


TEST2026_2                                      168



# 查看两张表的高水位线与碎片情况


```sql

set linesize 500


set pagesize 300


col owner for a15


col table_name for a10


set linesize 500


set pagesize 300


col owner for a15


col table_name for a10


SELECT owner, table_name, num_rows,


ROUND((blocks*8)/1024,2) "HIGH_WATER_MB",


ROUND((num_rows*avg_row_len/1024/1024),2) "REAL_DATA_MB",


ROUND((blocks*8 - num_rows*avg_row_len/1024 - blocks*8*0.1)/1024,2) "WASTE_MB"


FROM dba_tables


WHERE owner = 'MARY' AND table_name in ('TEST2026','TEST2026_2');

```



OWNER           TABLE_NAME   NUM_ROWS HIGH_WATER_MB REAL_DATA_MB   WASTE_MB


--------------- ---------- ---------- ------------- ------------ ----------


MARY            TEST2026      5000000        166.54       123.98      25.91


MARY            TEST2026_2    5000000        166.54       123.98      25.91



# 删除全部数据


```sql

DELETE FROM mary.test2026;


DELETE FROM mary.test2026_2;


COMMIT;

```


# 重新收集统计信息


```sql

execute dbms_stats.gather_table_stats ('MARY','TEST2026');


execute dbms_stats.gather_table_stats ('MARY','TEST2026_2');

```


# 这里我们可以看到，数据量虽然全部删除，但是两张表的数据段大小并没有减少


```sql

col segment_name for a40


select segment_name,sum(bytes)/1024/1024 as "size_MB" from dba_segments where segment_name in ('TEST2026','TEST2026_2') and owner='MARY' group by segment_name;

```


SEGMENT_NAME                                size_MB


---------------------------------------- ----------


TEST2026                                        168


TEST2026_2                                      168



# 通过以下SQL能够更容易看到实际占用空间与碎片空间的比对。高水位线很高，碎片非常严重。


```sql

set linesize 500


set pagesize 300


 col owner for a15


col table_name for a10


set linesize 500


set pagesize 300


col owner for a15


col table_name for a10


SELECT owner, table_name, num_rows,


ROUND((blocks*8)/1024,2) "HIGH_WATER_MB",


ROUND((num_rows*avg_row_len/1024/1024),2) "REAL_DATA_MB",


ROUND((blocks*8 - num_rows*avg_row_len/1024 - blocks*8*0.1)/1024,2) "WASTE_MB"


FROM dba_tables


WHERE owner = 'MARY' AND table_name in ('TEST2026','TEST2026_2');

```




OWNER           TABLE_NAME   NUM_ROWS HIGH_WATER_MB REAL_DATA_MB   WASTE_MB


--------------- ---------- ---------- ------------- ------------ ----------


MARY            TEST2026            0        166.54            0     149.89


MARY            TEST2026_2          0        166.54            0     149.89



# 实验1：对mary.test2026表执行SHRINK


```sql

ALTER TABLE mary.test2026 ENABLE ROW MOVEMENT;


ALTER TABLE mary.test2026 SHRINK SPACE COMPACT;


ALTER TABLE mary.test2026 SHRINK SPACE CASCADE;

```



# 实验2：对mary.test2026_2执行MOVE（重新制造碎片后）


```sql

ALTER TABLE mary.test2026_2 MOVE;

```


# 重新收集统计信息


```sql

execute dbms_stats.gather_table_stats ('MARY','TEST2026');


execute dbms_stats.gather_table_stats ('MARY','TEST2026_2');

```


## 观测实验结果：


# 先检查索引情况：


可以看到SHRINK方式后索引状态正常，MOVE方式则导致MARY_PK索引失效。


```sql

set linesize 600


set pagesize 500


col INDEX_NAME for a20


col TABLE_OWNER for a20


col TABLE_NAME for a30


col STATUS for a30


select INDEX_NAME,TABLE_OWNER,TABLE_NAME,STATUS from dba_indexes where TABLE_OWNER='MARY';

```




INDEX_NAME           TABLE_OWNER          TABLE_NAME                     STATUS


-------------------- -------------------- ------------------------------ ------------------------------


SYS_C009491          MARY                 TEST2026                       VALID


MARY_PK              MARY                 TEST2026_2                     UNUSABLE


（特殊说明：在原实验中，我此处做出来的结果MARY_PK没有失效，因为所有数据都被清空的原因，但如果表中还有数据，索引一定是会失效的）




# 重建test2026_2表的失效索引


```sql

ALTER INDEX mary.MARY_PK REBUILD ONLINE;     

```




```sql

set linesize 500


set pagesize 300


col owner for a15


col table_name for a10


set linesize 500


set pagesize 300


col owner for a15


col table_name for a10


SELECT owner, table_name, num_rows,


ROUND((blocks*8)/1024,2) "HIGH_WATER_MB",


ROUND((num_rows*avg_row_len/1024/1024),2) "REAL_DATA_MB",


ROUND((blocks*8 - num_rows*avg_row_len/1024 - blocks*8*0.1)/1024,2) "WASTE_MB"


FROM dba_tables


WHERE owner = 'MARY' AND table_name in ('TEST2026','TEST2026_2');

```




OWNER           TABLE_NAME   NUM_ROWS HIGH_WATER_MB REAL_DATA_MB   WASTE_MB


--------------- ---------- ---------- ------------- ------------ ----------


MARY            TEST2026            0           .01            0        .01


MARY            TEST2026_2          0             0            0          0


（特殊说明：本次实验使用的数据段过于连续，并且是删除所有数据，所以实验结果并不明显，SHRINK方式仅残留了0.01MB的使用空间，在生产环境中剩余的残留空间会更多）




SHRINK完成后观察：


1、段大小明显下降，但仍有少量碎片残留（高水位没有降到最低）


2、索引状态正常，无需重建


3、全表扫描的逻辑读显著减少，但不是最优




MOVE完成后观察：


1、高水位线彻底归零，段大小降到了接近真实数据量 + PCTFREE 的水平


2、碎片清理非常干净，全表扫描逻辑读达到接近TRUNCATE的效果


3、但操作期间需要额外约等于表大小的空间（新旧段同时存在）


4、所有索引失效，必须重建




## 五、实验结论与选型建议




通过这次对比可以清晰看到：




MOVE在碎片整理效果上明显优于SHRINK，高水位线清理得更彻底，后续全表扫描性能更好，空间释放也更干净。但它需要额外空间，操作时锁表时间相对更长（11g以上可以用ONLINE方式缓解）。




SHRINK操作更灵活，对空间要求低，大部分过程在线，但碎片整理效果稍差，有时还会残留少量碎片。




我的实际建议：


1、如果空间充足、有维护窗口，优先使用MOVE，长远收益更高；


2、如果空间紧张，或者业务不能接受较长时间锁表，就选择SHRINK。




不管哪种方式，都强烈建议在业务低峰期或维护窗口操作。SHRINK的最后一步仍有锁，MOVE更需要提前评估空间。




操作完成后，建议重新收集统计信息：




表碎片整理看似简单，实际操作时需要综合考虑业务窗口、可用空间和性能要求。希望这次完整的实验记录能给大家一些参考。




你在生产环境中主要用哪种方式处理表碎片？欢迎在评论区分享你的经验或遇到的问题。




—— 睿 | Oracle性能优化老司机


专注硬核干货，欢迎一起卷技术！





来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/70047128/viewspace-3174650/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： Oracle 19c Active Data Guard DML 重定向：让只读备库也能“写”！
下一篇： Oracle闪回技术全景实战：Flashback全家桶，从误删数据到完美恢复的终极指南
请登录后发表评论 登录
全部评论
marui
要得到你想要的某样东西，最可靠的办法是让你自己配得上它。

注册时间：2025-07-21

  • 博文量
    30
  • 访问量
    31696

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593