干Oracle DBA这些日子表碎片和索引碎片是我在生产环境里处理频率最高的一类问题。不管是几百万行的业务表还是几千万行的流水大表只要经过大量DELETE和UPDATE段内部就开始出现空洞查询变慢、空间报表难堪更麻烦的是后面还会带出索引失效、重建空间不足这类衍生事故。这篇文章是我个人多年处理Oracle表与索引碎片的完整实践记录从碎片成因、量化判断、具体操作到常见坑尽量一次讲透。适合刚入门的DBA照着排查也适合遇到大表收缩迟迟不敢动手的同行参考。1. 先搞清楚碎片到底是怎么产生的1.1 表碎片的本质空间闲置与行迁移Oracle里的普通表是堆表你INSERT数据时服务器进程会找有空闲空间的块把行放进去。DELETE并不是把行从物理块里“擦掉”而是给行打上删除标记块里那块空间被保留起来。后续的INSERT如果分配到同一个块新行可以复用这些空洞但问题是块内的旧行和新行密度都不一样反复地删、插、改之后一个8KB的块里可能只住了几行块数量越来越多段被白白撑大这就是最常见的表碎片。还有个更隐蔽的东西叫行迁移。当UPDATE把一行的长度改得比较大比如把VARCHAR2从10个字符改成200个字符原来的块放不下了Oracle会把整行搬到另一个块原块只留下一个“指向新块”的地址。以后每次读这行都需要先访问原块再跳转新块两次I/O起步这就是CHAIN_CNT上涨的根源。它既是碎片的一种也是SQL变慢的重要原因。另一个绕不开的概念是高水位线HWM。段里已经使用过的块其范围由HWM标记HWM以上的块从来没有被写过数据。你DELETE掉大量行甚至DELETE全表HWM都不会自动降下来段空间依然占着。只有把HWM以下的空洞重新填满或者主动重排段结构才是真正的收缩。打个比方停车场原来画了100个车位你租了整个停车场后来车走得只剩20辆剩下80个空位管理员也不会把停车场面积退给你。新来的车会往空位停但空位分布得越零散进出找位越费劲租金还一分不少。表段碎片就是这么个状态。1.2 索引碎片叶子块分裂与死条目索引的结构是B-Tree叶子块存放键值和对应的ROWID。当你不断插入新键值叶子块一旦放满Oracle会做一次块分裂把一个满块拆成两个半满的块。分裂本身就会把块内空间打散这是索引碎片的第一个来源。最典型的是按序列或时间戳生成主键的表每次插入几乎都打在最右侧的叶子块上主键索引分裂频率远高于普通普通字段索引经验上主键索引的LEAF_BLOCKS膨胀率也最吓人。第二个来源是删除。索引里的删除条目并不会在事务提交的瞬间物理移除Oracle有延迟清理机制删除标记一直攒到后续访问或凌晨清理任务才逐步回收。如果业务是白天大批量DELETE、晚间跑批再大量插入索引叶子块里经常混着大量“死条目”查询本来定位到一个块就能扫完100条记录结果要跨好几个块去跳过那些已经删除的键值。索引碎片的危害和表碎片不同。表碎片主要浪费空间、拖慢全表扫描索引碎片除了浪费空间还会增加B-Tree的层级和扫描成本。一个本应三层就能访问到的索引如果叶子块之间支离破碎实际访问路径可能要多跨几个块数据量大的时候差异非常明显。2. 动手前先定量碎片程度怎么判断2.1 从统计信息和段信息看硬数据我一直反对“每月无脑全库重建索引”这种操作大库这么干既浪费时间又触发大量I/O。判断碎片第一步是收集统计信息让数据说话。EXEC DBMS_STATS.GATHER_TABLE_STATS(SCOTT, BIG_TABLE, CASCADE TRUE);然后查表的统计信息SELECT table_name, blocks, empty_blocks, avg_row_len, chain_cnt FROM dba_tables WHERE owner SCOTT AND table_name BIG_TABLE;这里的BLOCKS是该表分配的数据块数EMPTY_BLOCKS是HWM以上从来没用过的块数。如果EMPTY_BLOCKS占BLOCKS的比例很高说明当初表被撑得很大数据删了但段没缩回来。CHAIN_CNT大于0说明存在行迁移值得关注。再查段大小和实际有行的块数SELECT segment_name, segment_type, bytes/1024/1024 AS size_mb FROM dba_segments WHERE owner SCOTT AND segment_name BIG_TABLE; SELECT COUNT(DISTINCT dbms_rowid.rowid_block_number(rowid)) AS used_blocks FROM SCOTT.BIG_TABLE;USED_BLOCKS是当前实际含有行数据的块数拿它去对比DBA_SEGMENTS里的段块数比值一下子就能看出空洞有多大。我习惯把这三个查询拼进一个巡检脚本每次跑批完直接输出比率省得反复拼SQL。2.2 用DBMS_SPACE看块内部空闲分布段大小的对比只能说明整体空间利用率块内部是不是碎得一塌糊涂还要用DBMS_SPACE包的SPACE_USAGE过程来看。它会按空闲率把块分成几个区间FS1表示块内空闲空间在0到25%FS2是25%到50%FS3是50%到75%FS4是75%到100%。SET SERVEROUTPUT ON DECLARE v_unformatted_blocks NUMBER; v_unformatted_bytes NUMBER; v_fs1_blocks NUMBER; v_fs1_bytes NUMBER; v_fs2_blocks NUMBER; v_fs2_bytes NUMBER; v_fs3_blocks NUMBER; v_fs3_bytes NUMBER; v_fs4_blocks NUMBER; v_fs4_bytes NUMBER; v_full_blocks NUMBER; v_full_bytes NUMBER; BEGIN DBMS_SPACE.SPACE_USAGE( SCOTT, BIG_TABLE, TABLE, NULL, v_unformatted_blocks, v_unformatted_bytes, v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes, v_fs3_blocks, v_fs3_bytes, v_fs4_blocks, v_fs4_bytes, v_full_blocks, v_full_bytes ); DBMS_OUTPUT.PUT_LINE( U || v_unformatted_blocks || FS1 || v_fs1_blocks || FS2 || v_fs2_blocks || FS3 || v_fs3_blocks || FS4 || v_fs4_blocks || FULL || v_full_blocks ); END; /如果跑出来FS3、FS4数量占比很高说明大量块里只有少量行甚至几乎没有有效数据这种块内部碎片就是SHRINK的打击目标。索引段也能用类似方式查只是SEGMENT_TYPE传INDEX不过索引一般更建议直接看LEAF_BLOCKS和BLEVEL综合判断。2.3 我的经验阈值什么时候该动手碎片处理没有官方“绝对值”标准我这些年总结的参考阈值是这么定的表段FS4和FS3的块合计占比超过30%或者EMPTY_BLOCKS超过段总块的20%就值得处理行迁移CHAIN_CNT超过总行数的2%优先处理因为它直接影响查询I/O索引LEAF_BLOCKS膨胀到理论值相当于DISTINCT_KEYS数的1.5倍以上或者BLEVEL超过3考虑REBUILD高频DELETE表哪怕看起来空间不大只要有定期大量删除也要纳入周期性维护清单。这些阈值不是拍脑袋是结合生产环境实测效果得出的。低于这个比例你去动表回收的空间有限却要承担锁表和索引失效风险不划算高于这个比例还不动碎片红利白白浪费还可能让SQL执行计划劣化。3. 表碎片的三种处理方案3.1 ALTER TABLE MOVE离线重排干净利落最简单的重排语句ALTER TABLE SCOTT.BIG_TABLE MOVE;MOVE会在段内部重新组织行把HWM降到实际数据位置清掉所有空洞效果最彻底。但代价是表在MOVE期间有DDL锁业务读写会阻塞所以只能在停机窗口或者低峰期操作。还有一个最关键的点MOVE会改变表的ROWID表上的所有索引失效必须重建。MOVE时加UPDATE INDEXES可以让Oracle顺手维护索引ALTER TABLE SCOTT.BIG_TABLE MOVE UPDATE INDEXES;这个语法确实省事但我的习惯是大表不加。因为大索引太多时UPDATE INDEXES阶段耗时很长而且一旦半途失败索引状态变UNUSABLE恢复起来很麻烦。不如MOVE之后单独生成REBUILD脚本分步处理每步都能看到进度。MOVE还有一个空间前提目标表空间需要约等于表当前段大小的额外空间。如果剩余空间不够可以先MOVE到空间充裕的新表空间再MOVE回来等于借道中转。大表移动时可以配合PARALLEL和NOLOGGING提升速度ALTER TABLE SCOTT.BIG_TABLE PARALLEL 8 NOLOGGING; ALTER TABLE SCOTT.BIG_TABLE MOVE UPDATE INDEXES; ALTER TABLE SCOTT.BIG_TABLE NOPARALLEL LOGGING;注意NOLOGGING如果表空间是FORCE LOGGING模式则不会生效而且并行会拉高I/O别选在业务高峰硬跑。LOB段也不能靠MOVE重排需要单独处理LOB分区。3.2 SHRINK SPACE在线的折中方案如果表不能接受长时间离线可以用SHRINKALTER TABLE SCOTT.BIG_TABLE ENABLE ROW MOVEMENT; ALTER TABLE SCOTT.BIG_TABLE SHRINK SPACE;SHRINK的本质是重写段内部的行把行往段前部挪然后逐步降低HWM。它的特点是可以分段执行先把行挪好再降HWMALTER TABLE SCOTT.BIG_TABLE SHRINK SPACE COMPACT; -- 观察业务稳定后再执行 ALTER TABLE SCOTT.BIG_TABLE SHRINK SPACE;COMPACT阶段做行迁移但不降HWM对业务的锁影响小真正降HWM的第二个阶段窗口更短。这种两段式思路非常适合白天还有少量读写的表。但SHRINK有几个硬限制开启ROW MOVEMENT后会改变ROWID如果表上有基于ROWID的逻辑或物化视图、外部依赖要提前评估系统表、IOT、含物化视图日志的表、部分LOB场景不支持SHRINK操作前必须翻官方限制清单。还有一点我踩过坑的SHRINK本质是写UNDO的在线操作如果UNDO表空间小并发DML一多很容易撑爆UNDO直接报ORA-01555或者ORA-13756。所以SHRINK建议错峰、调小UNDO压力必要时候拆成COMPACT和最终收缩两步走。提示执行SHRINK之前一定要确认 ROW MOVEMENT 已开启否则直接报ORA-10631之类错误这在生产上很常见。3.3 EXPDP/CTAS重导超大表终极大招对于几百GB甚至TB级的表MOVE和SHRINK都可能因为空间、时间、锁问题干不下去。这时候最稳的方案是逻辑重导EXPDP导出、DROP原表、重建表、IMPDP导入。虽然步骤多但效果最好——新表段从头分配空间利用率和存储参数都能重新规划。如果不想做全库级DP我常用CTAS手工重建思路ALTER TABLE SCOTT.BIG_TABLE RENAME TO BIG_TABLE_OLD; CREATE TABLE SCOTT.BIG_TABLE AS SELECT * FROM SCOTT.BIG_TABLE_OLD WHERE 10; -- 分批按范围插入控制每批事务和UNDO INSERT INTO SCOTT.BIG_TABLE SELECT * FROM SCOTT.BIG_TABLE_OLD WHERE id BETWEEN 1 AND 10000000; COMMIT; -- 循环处理剩余区间最后补索引、触发器、权限CTAS方案的优点是可以自己控制并行度、批量提交节奏、甚至顺带调整PCTFREE和压缩选项比一条MOVE在调度上更灵活。缺点是停机时间通常比MOVE更长且外键、触发器、同义词、授权这些都要人工对接。我只在MOVE和SHRINK都评估不行时才用这招一般用在核心大表换存储结构的场景。4. 索引重建和合并那些细节4.1 REBUILD和COALESCE怎么选索引碎片处理有两个基础操作REBUILD和COALESCE。REBUILD是重新生成一棵B-Tree新段按逻辑顺序紧凑排列碎片率大幅下降段大小也可能减小这是最彻底的手段ALTER INDEX SCOTT.IDX_BIG_T_ID REBUILD;生产库优先用ONLINE方式让DML在重建期间不中断ALTER INDEX SCOTT.IDX_BIG_T_ID REBUILD ONLINE;而COALESCE是原地把相邻的半空叶子块合并不重新分配段空间因此不会减少段占用锁竞争也小得多。它的定位是“碎片不严重、空间不敏感、希望快速整理”时使用ALTER INDEX SCOTT.IDX_BIG_T_ID COALESCE;我的选择原则很简单索引碎片率极高、BLEVEL异常高或者段空间必须回收用REBUILD只是日常整理、块内空间离散用COALESCE。REBUILD ONLINE虽然好但需要几乎等量的表空间临时表空间也有额外消耗空间紧张时先评估再加文件。4.2 一套稳妥的索引重建步骤少走弯路的重建流程我自己固定在脚本里-- 1. 先查目标索引现状 SELECT index_name, blevel, leaf_blocks, distinct_keys, status FROM dba_indexes WHERE owner SCOTT AND index_name IDX_BIG_T_ID; -- 2. 在线重建按服务器CPU情况定并行度 ALTER INDEX SCOTT.IDX_BIG_T_ID REBUILD ONLINE PARALLEL 4; -- 3. 务必恢复NOPARALLEL否则索引一直被并行扫描反而拖慢查询 ALTER INDEX SCOTT.IDX_BIG_T_ID NOPARALLEL; -- 4. 重建后收集索引统计信息 EXEC DBMS_STATS.GATHER_INDEX_STATS(SCOTT, IDX_BIG_T_ID);这里我要多说一句PARALLEL的坑REBUILD时加PARALLEL能显著提速但如果重建完忘了NOPARALLEL这个索引后续查询会被优化器当成并行索引来用小查询反而变慢。而且并行重建是否真的值得取决于服务器的CPU核数和IO能力我一般只在4核以上、IO有余量的机器上用。还有民间操作是DROP INDEX再CREATE INDEX除非索引已经烂到无法REBUILD否则我不建议。DROP瞬间查询计划会失效优化器可能走全表扫描加上CREATE阶段的排他锁风险比REBUILD大很多。4.3 分区表和全局索引的坑分区表场景比普通表复杂得多。移动分区表时如果一个分区被MOVE分区索引或者全局索引可能直接变成UNUSABLE这是生产事故的高发区。ALTER TABLE SCOTT.BIG_PART_TABLE MOVE PARTITION P_2024 TABLESPACE NEW_TS; -- 移动完必须检查索引状态 SELECT index_name, partition_name, status FROM dba_ind_partitions WHERE index_owner SCOTT AND index_name IDX_PART_T_ID;如果状态是UNUSABLE需要单独重建受影响的索引分区或全局索引ALTER INDEX SCOTT.IDX_PART_T_ID REBUILD PARTITION P_2024;重建全局索引时要注意它不像分区索引可以直接指定分区一个大全局索引重建可能耗时数小时最好评估是不是有别的方案避免频繁分区移动。我处理EBS这类大量分区表的经验是在脚本里把“移动分区”和“索引重建”绑定成一个原子任务宁可多等一会不要留半拉子状态。5. 现场实录常见问题和排查清单5.1 MOVE之后索引失效应用秒报错这是所有碎片处理里最经典的翻车现场。MOVE表后忘了UPDATE INDEXES或者漏了重建脚本应用立刻报ORA-01502“索引处于不可用状态”之类的错误查询计划直接崩。解决思路是MOVE前先导出全表索引清单SELECT ALTER INDEX || index_name || REBUILD ONLINE; FROM dba_indexes WHERE table_owner SCOTT AND table_name BIG_TABLE;MOVE完成后批量执行这段脚本。操作顺序上先确保核心索引重建成功再处理次要索引。重建期间先放读流量、再放写流量避免并发DML干扰。5.2 SHRINK等待、UNDO膨胀和报错SHRINK遇到并发DML时会频繁出现事务等待甚至ORA-13756这类错误。最常见的两个前置条件没满足忘开ROW MOVEMENT或者UNDO表空间太小。我的建议是生产库跑SHRINK前先确认UNDO剩余空间再单独看目标表是否有长事务在跑。另一个容易忽略的点SHRINK执行期间会产生大量UNDO和REDOSHRINK完成后段是紧凑了但数据库日志量可能暴涨。所以不要在业务高峰跑也不要在ARCHIVELOG空间吃紧的时候跑。真扛不住就用两段式SHRINK把最耗时的COMPACT阶段放到白天低峰把降HWM阶段安排在停机窗口。5.3 重建索引空间不足怎么救火REBUILD ONLINE最怕空间不够报ORA-01654或者ORA-01653的现场我处理过不少次。解决顺序一般是先看索引所在表空间的剩余空间和段大小算一算是否够重建一倍需求空间不够就加一个数据文件扩完之后重建如果加文件也不行改用COALESCE先整理一些空间观察效果或者把索引重建到别的表空间成功后再改回原表空间借壳周转。还有一种更省空间的做法把索引先DROP再重建。虽然我前面说一般不推荐但在空间完全不足以支撑ONLINE REBUILD的极端条件下这可能是唯一能落地的方案。真到了这一步必须在维护窗口执行并提前通知所有依赖该索引的报表和批处理任务。5.4 周期性碎片的监控和维护建议碎片不是处理一次就一劳永逸我习惯在巡检脚本里加四个固定检查项每周扫描DBA_TABLES中EMPTY_BLOCKS比例超过20%的表每周扫描索引LEAF_BLOCKS与DISTINCT_KEYS比值异常的对象每月对删除量大的表计划一次SHRINK或MOVE每季度对全库做一次段顾问参考OUTCOME为RECLAIM的表再人工复核。现在Oracle有Segment AdvisorEM界面或者命令行都能跑会给出“是否需要收缩以及采用哪种方式”的建议。但我不会盲信它工具建议只是初筛最终动手前我还是会自己跑一遍SPACE_USAGE和行块统计SQL交叉验证。多做这一步比事后救火强太多。最后再分享一个小经验碎片处理这件事核心不在“用哪条命令”而在于理解业务DML模式。那些每天跑批大量DELETE再INSERT的表如果你不针对它的写入节奏安排碎片整理窗口任何方案都是治标不治本。搞清楚数据怎么进、怎么出、什么时段最安静比记住几条ALTER语法值钱得多。