必须先按owner+segment_name聚合再排序，因分区表、lob表、索引等会拆分为多个段，直接查dba_segments取top 10会淹没真实业务表而显示大量碎片段，聚合后才能准确反映逻辑实体的真实空间占用。
查 DBA_SEGMENTS 时必须先按 owner + segment_name 聚合再排序
直接 SELECT * FROM dba_segments WHERE tablespace_name = 'USERS' ORDER BY bytes DESC FETCH FIRST 10 ROWS ONLY 看起来快，但结果不可信。一张分区表、带 LOB 的表或含多个索引的表，在 dba_segments 中会拆成十几个甚至上百个段：比如 SALES_P2025Q1（分区）、SYS_LOB000012345C00002$$（LOB 段）、SALES_IDX1（索引）——它们各自排大小，TOP 10 里全是碎片，真正吃空间的逻辑表反而被淹没。
正确做法是把同一 owner + 同一 segment_name 下所有段（无论 segment_type 是 TABLE/INDEX/LOBSEGMENT）加总，视为一个业务实体的完整存储开销：
SELECT s.owner, s.segment_name,
ROUND(SUM(s.bytes)/1024/1024) size_mb,
LISTAGG(DISTINCT s.segment_type, ',') WITHIN GROUP (ORDER BY s.segment_type) types
FROM dba_segments s
WHERE s.tablespace_name = 'USERS'
GROUP BY s.owner, s.segment_name
ORDER BY size_mb DESC
FETCH FIRST 10 ROWS ONLY;
-
LISTAGG(...)能一眼看出该segment_name是否混杂了 INDEX 或 LOBSEGMENT - 若
types显示TABLE,LOBSEGMENT,INDEX，说明这张表的物理存储分散在三类段里，聚合后才反映真实压力 - Oracle 11g 及更早请用子查询套
ROWNUM，但必须确保外层已按SUM(bytes)排序完成
区分 TABLE 段和非 TABLE 段类型，避免误判主体
聚合后仍要盯紧 types 字段构成。如果 TOP 10 里大量是 INDEX 或 LOBSEGMENT，说明业务表本身不大，但索引设计或大字段使用不合理——比如一个 CLOB 字段存了上万条日志，实际空间全在 LOB 段里，而基表段只占几 MB。
这时不能只看 segment_name 名字，得顺藤摸瓜：
- 对
segment_type = 'LOBSEGMENT'的记录，查dba_lobs回溯真实所属表：SELECT table_name, column_name FROM dba_lobs WHERE segment_name = 'SYS_LOB000012345C00002$$' - 对
segment_type = 'INDEX'，用dba_indexes.table_name回溯基表：SELECT table_name FROM dba_indexes WHERE index_name = 'SALES_IDX1' - 若
owner是SYSTEM或SYSAUX，别急着处理，先确认是否属 AWR 快照、统计信息历史等系统组件
关联 dba_tables 补行数，识别高水位与真·大户
光看字节数不够。一张 10GB 的表只有 10 行，大概率是高水位或历史碎片；另一张 8GB 的表有 5000 万行，才是真·业务大户。所以聚合完后，最好左连接 dba_tables 补充 num_rows 和 blocks：
SELECT s.owner, s.segment_name,
ROUND(SUM(s.bytes)/1024/1024) size_mb,
t.num_rows,
ROUND((t.num_rows * t.avg_row_len) / 1024 / 1024, 2) used_mb,
ROUND(SUM(s.bytes)/1024/1024 - (t.num_rows * t.avg_row_len) / 1024 / 1024, 2) waste_mb
FROM dba_segments s
LEFT JOIN dba_tables t ON s.owner = t.owner AND s.segment_name = t.table_name
WHERE s.tablespace_name = 'USERS'
AND t.table_name IS NOT NULL
GROUP BY s.owner, s.segment_name, t.num_rows, t.avg_row_len
ORDER BY waste_mb DESC
FETCH FIRST 10 ROWS ONLY;
-
waste_mb高，且num_rows小 → 高水位未回落，考虑SHRINK SPACE或MOVE -
waste_mb高，且num_rows大 → 真实数据膨胀，检查业务写入逻辑或归档策略 - 注意：
dba_tables中的统计信息必须新鲜，否则avg_row_len严重失真；可用SELECT last_analyzed, stale_stats FROM dba_tab_statistics核查
用 DBMS_SPACE.SPACE_USAGE 查单表真实块级碎片
dba_segments 和 dba_tables 都是粗粒度估算。要定位某张表内部空闲块分布（比如哪些块 0–25% 空、哪些块 75–100% 空），必须调用 DBMS_SPACE.SPACE_USAGE。它能返回比 BLOCKS × PCTFREE 更准的块级使用细节，尤其适合判断是否值得 SHRINK：
set serveroutput on
DECLARE
v_unformatted_blocks NUMBER;
v_fs1_blocks NUMBER; -- 0-25% free
v_fs2_blocks NUMBER; -- 25-50% free
v_fs3_blocks NUMBER; -- 50-75% free
v_fs4_blocks NUMBER; -- 75-100% free
v_full_blocks NUMBER;
BEGIN
DBMS_SPACE.SPACE_USAGE(
segment_owner => 'SCOTT',
segment_name => 'EMP',
segment_type => 'TABLE',
unformatted_blocks => v_unformatted_blocks,
fs1_blocks => v_fs1_blocks,
fs2_blocks => v_fs2_blocks,
fs3_blocks => v_fs3_blocks,
fs4_blocks => v_fs4_blocks,
full_blocks => v_full_blocks
);
DBMS_OUTPUT.PUT_LINE('FS1 blocks (0-25% free): ' || v_fs1_blocks);
DBMS_OUTPUT.PUT_LINE('FS2 blocks (25-50% free): ' || v_fs2_blocks);
DBMS_OUTPUT.PUT_LINE('FS3 blocks (50-75% free): ' || v_fs3_blocks);
DBMS_OUTPUT.PUT_LINE('FS4 blocks (75-100% free): ' || v_fs4_blocks);
DBMS_OUTPUT.PUT_LINE('Full blocks: ' || v_full_blocks);
END;
/
- 若
FS1 + FS2块数占比 > 40%，且full_blocks很低 → 表内碎片严重，SHRINK SPACE COMPACT有效 - 若
unformatted_blocks很高 → 表刚经历大量删除，但 HWM 未下降，SHRINK SPACE可释放空间 - 此过程仅适用于 ASSM 表空间；MSSM 表空间需改用
ANALYZE TABLE ... VALIDATE STRUCTURE CASCADE（但会锁表）
真正的碎片判断难点不在“怎么查”，而在于分清：是逻辑表本身膨胀，还是段碎片掩盖了真实归属；是统计信息过期导致误判，还是高水位长期悬停却没人动它。多数人卡在第一步聚合就错了，后面越查越偏。
