oracle表空间碎片本质是空闲区分散不连续，真正反映碎片程度的是空闲区能否被有效利用；需通过dba_free_space分析空闲区分布，并用dbms_space.space_usage或object_space_usage评估段级逻辑碎片。
查dba_free_space里未合并的空闲块总和
oracle表空间碎片本质是空闲区（extent）分散、不连续，导致无法分配大对象。直接看dba_free_space中所有空闲记录的bytes加总，只是“空闲总量”，不是“碎片量”。真正反映碎片程度的是：这些空闲区是否能被有效利用。所以第一步要确认当前未合并的空闲块数量与大小分布。
执行以下查询可快速看到各空闲区大小段的频次：
SELECT TABLESPACE_NAME, ROUND(SUM(BYTES)/1024/1024, 2) AS "FREE_MB", COUNT(*) AS "FREE_EXTENTS", ROUND(MIN(BYTES)/1024/1024, 2) AS "MIN_MB", ROUND(MAX(BYTES)/1024/1024, 2) AS "MAX_MB", ROUND(AVG(BYTES)/1024/1024, 2) AS "AVG_MB" FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME ORDER BY FREE_MB DESC;
- 如果
MIN_MB极小（比如几KB）、MAX_MB极大（几百MB），且FREE_EXTENTS远大于FREE_MB / AVG_MB的理论值，说明存在大量小碎片 - 注意：该视图只反映当前快照，不包含已分配但未使用的高水位线（HWM）以上空间
- 必须用
DBA权限用户执行，普通用户查USER_FREE_SPACE仅限本用户默认表空间
用DBMS_SPACE.SPACE_USAGE估算段级碎片
单个表或索引的碎片更影响性能。Oracle提供DBMS_SPACE.SPACE_USAGE过程，可返回某个段（segment）的已用、未用、回收中（ASSM下）等块级统计，比NUM_ROWS × AVG_ROW_LEN估算更准。
示例：查表EMPLOYEES在USERS表空间中的空间使用细节：
DECLARE
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;
BEGIN
DBMS_SPACE.SPACE_USAGE(
segment_owner => 'HR',
segment_name => 'EMPLOYEES',
segment_type => 'TABLE',
partition_name => NULL,
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('FS1 (0-25% free): ' || l_fs1_bytes);
DBMS_OUTPUT.PUT_LINE('FS2 (25-50% free): ' || l_fs2_bytes);
DBMS_OUTPUT.PUT_LINE('FS3 (50-75% free): ' || l_fs3_bytes);
DBMS_OUTPUT.PUT_LINE('FS4 (75-100% free): ' || l_fs4_bytes);
END;
-
FS1~FS4对应块内空闲空间比例区间，值越大说明该段内大量数据块处于“半空”状态，即逻辑碎片严重 - 此过程仅适用于
ASSM（自动段空间管理）表空间；MANUAL方式需用ANALYZE TABLE ... LIST CHAINED ROWS - 调用前确保
DBMS_OUTPUT.ENABLE已启用，否则无输出
写脚本汇总所有段的FS3+FS4空闲块占比
碎片影响大的典型特征是：大量块处于FS3（50–75%空闲）或FS4（75–100%空闲）。把这些块对应的字节数加总，再除以表空间总空闲量，就能得到一个“高碎片空闲占比”指标，比单纯数空闲区个数更贴近实际浪费。
下面是一个可直接运行的匿名块脚本（需DBA权限）：
SET SERVEROUTPUT ON
DECLARE
v_ts_name VARCHAR2(30);
v_total_free NUMBER := 0;
v_frag_free NUMBER := 0;
v_frag_ratio NUMBER;
CURSOR ts_cur IS
SELECT DISTINCT TABLESPACE_NAME FROM DBA_TABLESPACES
WHERE CONTENTS = 'PERMANENT' AND STATUS = 'ONLINE';
BEGIN
FOR ts IN ts_cur LOOP
v_ts_name := ts.TABLESPACE_NAME;
SELECT NVL(SUM(BYTES), 0) INTO v_total_free
FROM DBA_FREE_SPACE WHERE TABLESPACE_NAME = v_ts_name;
<pre class="brush:php;toolbar:false;">-- 汇总该表空间下所有段的FS3+FS4空闲字节
SELECT NVL(SUM(fs3_bytes + fs4_bytes), 0) INTO v_frag_free
FROM (
SELECT
s.owner, s.segment_name, s.segment_type,
NVL(fs.fs3_bytes, 0) AS fs3_bytes,
NVL(fs.fs4_bytes, 0) AS fs4_bytes
FROM DBA_SEGMENTS s,
TABLE(DBMS_SPACE.OBJECT_SPACE_USAGE(s.owner, s.segment_name, s.segment_type)) fs
WHERE s.TABLESPACE_NAME = v_ts_name
AND s.owner NOT IN ('SYS','SYSTEM')
);
IF v_total_free > 0 THEN
v_frag_ratio := ROUND(v_frag_free / v_total_free * 100, 2);
IF v_frag_ratio > 30 THEN
DBMS_OUTPUT.PUT_LINE(v_ts_name || ': FRAG_FREE=' ||
ROUND(v_frag_free/1024/1024,1) || 'MB / TOTAL_FREE=' ||
ROUND(v_total_free/1024/1024,1) || 'MB (' || v_frag_ratio || '%)');
END IF;
END IF;END LOOP; END;
- 该脚本只对非系统用户段做分析，避免
SYS对象干扰结果 -
DBMS_SPACE.OBJECT_SPACE_USAGE是DBMS_SPACE.SPACE_USAGE的集合版，适合批量处理 - 注意：若某表空间含大量
LOB段，OBJECT_SPACE_USAGE可能报ORA-13516，需加异常捕获或跳过LOB类型
为什么不能只依赖coalesce或shrink space来“修复”
很多人查出碎片后第一反应是立刻ALTER TABLESPACE ... COALESCE或ALTER TABLE ... SHRINK SPACE。但这两者作用范围和前提完全不同，乱用反而引发问题。
-
COALESCE只对DICTIONARY-MANAGED表空间有效，且仅合并相邻空闲extent——而绝大多数10g+数据库用的是ASSM，执行它没任何效果 -
SHRINK SPACE要求表启用行移动（ENABLE ROW MOVEMENT），且会触发大量I/O和锁，线上高峰期慎用；对索引组织表（IOT）或含LONG列的表不支持 - 真正降低碎片的关键动作其实是：预估对象增长、设置合理
INITIAL/NEXTextent大小、避免频繁INSERT+DELETE而不COMMIT，以及定期归档冷数据
碎片不是错误，而是空间管理策略与业务模式不匹配的信号。脚本算出来的数字，只是帮你定位“哪里不匹配”，而不是“一键修复”的开关。
