《Oracle碎片整理方法》
一、引言
在Oracle数据库的长期运行过程中，由于频繁的DML操作（如INSERT、UPDATE、DELETE）、表空间管理不当或存储参数配置不合理等原因，数据库对象（如表、索引）会产生碎片。碎片的存在会导致存储空间浪费、I/O性能下降、查询效率降低等问题，严重时甚至影响系统的整体稳定性。因此，定期进行碎片整理是Oracle数据库维护的重要环节。本文将系统介绍Oracle碎片的成因、识别方法及多种整理策略，帮助DBA高效解决碎片问题。
二、Oracle碎片的成因与分类
1. 表碎片的成因
（1）行迁移（Row Migration）：当UPDATE操作导致行数据长度超过原存储块空间时，Oracle会将整行迁移到新块，并在原位置留下指向新块的指针（称为"行链"）。频繁的行迁移会导致表数据分散存储，增加I/O开销。
（2）行链接（Row Chaining）：若单行数据本身超过一个数据块的大小（如包含LOB字段），Oracle会将数据分割存储在多个块中，形成行链接。这种情况在存储大对象时尤为常见。
（3）自由空间碎片：DELETE或UPDATE操作会释放块内的空间，但这些空间可能无法被后续插入操作重用，导致块内出现大量不连续的空闲空间。
2. 索引碎片的成因
（1）索引块分裂：当索引键值插入导致块空间不足时，Oracle会将块分裂为两个，原块保留部分键值，新块存储剩余键值。频繁的插入和删除会导致索引树结构失衡。
（2）删除操作残留：DELETE操作会标记索引键值为"已删除"，但不会立即释放空间，导致索引块内存在无效条目。
（3）不均匀的数据分布：若索引键值分布不均匀（如某些值出现频率极高），会导致部分索引块过载，而其他块利用率低下。
3. 表空间碎片的成因
（1）区（Extent）分配不连续：当表空间使用自动段空间管理（ASSM）或手动管理时，若频繁分配和释放区，会导致区在数据文件中分散存储。
（2）剩余空间不足：表空间中剩余空间被分割为多个小块，无法满足大对象的分配需求。
三、碎片识别方法
1. 表碎片识别
（1）使用DBMS_SPACE包分析表空间使用情况：
DECLARE
v_total_blocks NUMBER;
v_total_bytes NUMBER;
v_unused_blocks NUMBER;
v_unused_bytes NUMBER;
v_last_used_extent_file_id NUMBER;
v_last_used_extent_block_id NUMBER;
v_last_used_block NUMBER;
BEGIN
DBMS_SPACE.UNUSED_SPACE(
segment_owner => 'SCHEMA_NAME',
segment_name => 'TABLE_NAME',
segment_type => 'TABLE',
total_blocks => v_total_blocks,
total_bytes => v_total_bytes,
unused_blocks => v_unused_blocks,
unused_bytes => v_unused_bytes,
last_used_extent_file_id => v_last_used_extent_file_id,
last_used_extent_block_id => v_last_used_extent_block_id,
last_used_block => v_last_used_block
);
DBMS_OUTPUT.PUT_LINE('Total Blocks: ' || v_total_blocks);
DBMS_OUTPUT.PUT_LINE('Unused Blocks: ' || v_unused_blocks);
END;
/
（2）查询DBA_TABLES视图中的PCT_FREE和PCT_USED参数，结合实际使用率判断碎片程度。
（3）使用ANALYZE命令收集统计信息后，查询DBA_TABLES的EMPTY_BLOCKS和AVG_SPACE字段：
ANALYZE TABLE SCHEMA_NAME.TABLE_NAME COMPUTE STATISTICS;
SELECT empty_blocks, avg_space, avg_row_len
FROM dba_tables
WHERE owner='SCHEMA_NAME' AND table_name='TABLE_NAME';
2. 索引碎片识别
（1）查询DBA_INDEXES视图中的CLUSTERING_FACTOR、BLEVEL和LEAF_BLOCKS字段：
SELECT index_name, blevel, leaf_blocks, clustering_factor
FROM dba_indexes
WHERE owner='SCHEMA_NAME' AND table_name='TABLE_NAME';
（2）使用ANALYZE INDEX命令分析索引结构：
ANALYZE INDEX SCHEMA_NAME.INDEX_NAME VALIDATE STRUCTURE;
SELECT name, height, lf_blks, br_blks, del_lf_rows
FROM index_stats;
（3）计算索引碎片率：
SELECT
(1 - (SELECT SUM(bytes) FROM dba_segments
WHERE segment_name='INDEX_NAME' AND owner='SCHEMA_NAME') /
(SELECT SUM(bytes) FROM dba_extents
WHERE segment_name='INDEX_NAME' AND owner='SCHEMA_NAME')) * 100
AS fragmentation_percentage
FROM dual;
3. 表空间碎片识别
（1）查询DBA_FREE_SPACE视图分析剩余空间分布：
SELECT tablespace_name, COUNT(*) AS extents,
SUM(bytes)/1024/1024 AS free_space_mb,
MAX(bytes)/1024/1024 AS largest_extent_mb
FROM dba_free_space
WHERE tablespace_name='TABLESPACE_NAME'
GROUP BY tablespace_name;
（2）使用DBMS_SPACE.SPACE_USAGE过程分析段空间使用情况：
DECLARE
v_full_blocks NUMBER;
v_unformatted_blocks NUMBER;
v_unformatted_bytes NUMBER;
v_fs1_blocks NUMBER; -- 1-25% full
v_fs2_blocks NUMBER; -- 26-50% full
v_fs3_blocks NUMBER; -- 51-75% full
v_fs4_blocks NUMBER; -- 76-100% full
v_full_bytes NUMBER;
BEGIN
DBMS_SPACE.SPACE_USAGE(
segment_owner => 'SCHEMA_NAME',
segment_name => 'TABLE_NAME',
segment_type => 'TABLE',
space_id => NULL, -- For ASSM tablespaces
pct_used => NULL,
full_blocks => v_full_blocks,
unformatted_blocks => v_unformatted_blocks,
unformatted_bytes => v_unformatted_bytes,
fs1_blocks => v_fs1_blocks,
fs2_blocks => v_fs2_blocks,
fs3_blocks => v_fs3_blocks,
fs4_blocks => v_fs4_blocks,
full_bytes => v_full_bytes
);
-- 输出结果分析
END;
/
四、碎片整理方法
1. 表碎片整理
（1）ALTER TABLE MOVE方法：
原理：将表数据移动到新的段，重新组织数据存储结构，消除行迁移和行链接。
步骤：
-- 1. 创建临时表空间（可选）
CREATE TABLESPACE temp_ts DATAFILE '/path/to/temp01.dbf' SIZE 1G;
-- 2. 移动表到新表空间
ALTER TABLE schema_name.table_name MOVE
TABLESPACE new_tablespace
STORAGE (INITIAL 100M NEXT 50M MAXEXTENTS UNLIMITED)
PCTFREE 10 PCTUSED 40;
-- 3. 重建相关索引
ALTER INDEX schema_name.index_name REBUILD TABLESPACE new_tablespace;
-- 4. 更新统计信息
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA_NAME', 'TABLE_NAME');
优点：彻底整理碎片，可同时调整存储参数。
缺点：需要额外存储空间，可能影响生产环境性能。
（2）DBMS_REDEFINITION在线重定义：
原理：在表可用的前提下，通过物化视图日志实现表的在线重构。
步骤：
-- 1. 检查表是否可重定义
EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('SCHEMA_NAME', 'TABLE_NAME');
-- 2. 创建临时表结构
CREATE TABLE schema_name.table_name_tmp (
-- 与原表相同列定义
) TABLESPACE new_tablespace;
-- 3. 初始化重定义过程
BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE(
uname => 'SCHEMA_NAME',
orig_table => 'TABLE_NAME',
int_table => 'TABLE_NAME_TMP'
);
END;
/
-- 4. 同步数据（可选）
EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE('SCHEMA_NAME', 'TABLE_NAME', 'TABLE_NAME_TMP');
-- 5. 完成重定义
BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE(
uname => 'SCHEMA_NAME',
orig_table => 'TABLE_NAME',
int_table => 'TABLE_NAME_TMP'
);
END;
/
-- 6. 清理临时表
DROP TABLE schema_name.table_name_tmp;
优点：无需额外存储空间，业务中断时间短。
缺点：实现复杂，对表结构有一定限制。
2. 索引碎片整理
（1）ALTER INDEX REBUILD方法：
原理：完全重建索引结构，消除块分裂和删除残留。
步骤：
-- 1. 重建单个索引
ALTER INDEX schema_name.index_name REBUILD
TABLESPACE new_tablespace
STORAGE (INITIAL 50M NEXT 25M);
-- 2. 重建表所有索引（PL/SQL示例）
BEGIN
FOR idx_rec IN (
SELECT index_name FROM dba_indexes
WHERE table_owner='SCHEMA_NAME' AND table_name='TABLE_NAME'
) LOOP
EXECUTE IMMEDIATE 'ALTER INDEX ' ||
'schema_name.' || idx_rec.index_name || ' REBUILD';
END LOOP;
END;
/
优点：操作简单，效果显著。
缺点：对大索引可能耗时较长。
（2）COALESCE方法：
原理：合并索引块中的空闲空间，不改变索引物理位置。
步骤：
ALTER INDEX schema_name.index_name COALESCE;
优点：无需额外存储空间，执行速度快。
缺点：仅合并空闲空间，不解决块分裂问题。
3. 表空间碎片整理
（1）收缩段（Shrink Segment）：
适用条件：表空间使用自动段空间管理（ASSM），且表有可用的空闲空间。
步骤：
-- 1. 启用行移动功能
ALTER TABLE schema_name.table_name ENABLE ROW MOVEMENT;
-- 2. 收缩段空间
ALTER TABLE schema_name.table_name SHRINK SPACE COMPACT; -- 仅重组数据
ALTER TABLE schema_name.table_name SHRINK SPACE; -- 重组并释放空间
-- 3. 收缩索引
ALTER INDEX schema_name.index_name SHRINK SPACE;
优点：在线操作，无需额外存储。
缺点：对大表可能影响性能。
（2）表空间重组：
原理：通过导出/导入或数据泵（Data Pump）重新加载数据。
步骤：
-- 1. 使用expdp导出表
expdp schema_name/password@db
tables=table_name
directory=DATA_PUMP_DIR
dumpfile=table_name.dmp
-- 2. 删除原表
DROP TABLE schema_name.table_name PURGE;
-- 3. 重新创建表结构
CREATE TABLE schema_name.table_name (...) TABLESPACE new_tablespace;
-- 4. 使用impdp导入数据
impdp schema_name/password@db
tables=table_name
directory=DATA_PUMP_DIR
dumpfile=table_name.dmp
优点：彻底解决碎片问题。
缺点：操作复杂，需要停机时间。
五、碎片整理最佳实践
1. 制定整理策略
（1）定期监控：每周通过脚本检查关键表的碎片率，当空闲块比例超过20%或行迁移率超过10%时触发整理。
（2）分级处理：对核心业务表采用在线重定义，对非核心表采用MOVE+REBUILD组合。
（3）批量操作：在低峰期执行多个表的整理，通过并行度控制资源占用。
2. 参数优化建议
（1）PCTFREE：设置合理的空闲空间比例（通常10%-20%），频繁更新的表可适当提高。
（2）INITIAL/NEXT：为表和索引设置合适的区大小，避免频繁分配。
（3）TABLESPACE配置：使用ASSM管理表空间，设置均匀的区大小（如1MB）。
3. 自动化脚本示例
以下是一个完整的碎片检测与整理脚本：
-- 碎片检测脚本
SET SERVEROUTPUT ON
DECLARE
v_table_name VARCHAR2(128);
v_owner VARCHAR2(128);
v_frag_pct NUMBER;
v_move_sql VARCHAR2(4000);
BEGIN
DBMS_OUTPUT.PUT_LINE('Starting Fragmentation Analysis...');
FOR tab_rec IN (
SELECT owner, table_name,
(1 - (nvl(sum(bytes),0)/
(SELECT sum(bytes) FROM dba_segments
WHERE segment_name=t.table_name AND owner=t.owner))) * 100 AS frag_pct
FROM dba_extents e, dba_tables t
WHERE e.segment_name=t.table_name AND e.owner=t.owner
GROUP BY owner, table_name
HAVING (1 - (nvl(sum(bytes),0)/
(SELECT sum(bytes) FROM dba_segments
WHERE segment_name=t.table_name AND owner=t.owner))) * 100 > 20
) LOOP
v_owner := tab_rec.owner;
v_table_name := tab_rec.table_name;
v_frag_pct := tab_rec.frag_pct;
DBMS_OUTPUT.PUT_LINE('Table: ' || v_owner || '.' || v_table_name ||
' Fragmentation: ' || ROUND(v_frag_pct,2) || '%');
-- 生成MOVE语句
v_move_sql := 'ALTER TABLE ' || v_owner || '.' || v_table_name ||
' MOVE TABLESPACE USERS STORAGE (INITIAL 100M NEXT 50M)';
DBMS_OUTPUT.PUT_LINE('Generated SQL: ' || v_move_sql);
-- 实际执行时可取消注释
-- EXECUTE IMMEDIATE v_move_sql;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Analysis Completed.');
END;
/
六、总结
Oracle碎片整理是数据库性能优化的重要环节。通过系统识别表、索引和表空间的碎片程度，DBA可以选择ALTER TABLE MOVE、在线重定义、索引重建等合适的方法进行整理。在实际操作中，应结合业务特点制定分级整理策略，优化存储参数配置，并通过自动化脚本实现定期监控和处理。合理的碎片管理能够显著提升数据库I/O性能，减少存储空间浪费，保障系统长期稳定运行。
关键词：Oracle碎片整理、表碎片、索引碎片、表空间碎片、ALTER TABLE MOVE、DBMS_REDEFINITION、索引重建、COALESCE、收缩段、存储参数优化
简介：本文详细阐述了Oracle数据库中表、索引和表空间碎片的成因与分类，提供了多种碎片识别方法（包括DBMS_SPACE包、统计信息分析等），系统介绍了ALTER TABLE MOVE、在线重定义、索引重建、COALESCE、收缩段等整理技术，并给出了参数优化建议和自动化脚本示例，帮助DBA有效解决碎片问题，提升数据库性能。
