如何排查Oracle中哪些Segment段占用了大量表空间

必须先按owner+segment_name+segment_type聚合SUM(bytes)，再ORDER BY SUM(bytes) DESC并FETCH FIRST 10 ROWS ONLY；直接用ROWNUM会因未排序导致结果失真，且需关联DBA_LOBS、DBA_INDEXES等视图才能准确定位真实占用主体。

直接查 dba_segments 就能定位真实占用大户，但必须聚合、排序、过滤三步到位，否则看到的前10名大概率是“假大空”——比如一堆小分区或零碎索引，而真正吃空间的 lob 段或主表被埋没。

为什么不能直接用 ROWNUM

ROWNUM 在 ORDER BY 之前就已分配，未排序时取前10行毫无意义。一张分区表有50个分区，DBA_SEGMENTS 里就有50条记录；一个带3个 CLOB 字段的表，会额外生成3个 LOBSEGMENT 记录；每个索引又单独成段。不先 GROUP BY 聚合，结果就是碎片化噪音。

• 必须先 SUM(bytes) 按逻辑单位合并（如同一张表的所有物理段）
• 聚合粒度推荐：owner, segment_name, segment_type —— 既区分 TABLE/INDEX/LOBSEGMENT，又避免把索引和表混在一起
• 别用 partition_name 参与分组，否则分区表会被拆得更碎，失去“表级”视角

怎么写 SQL 才能查出真实的 Top 10 占用段？

以下语句是目前最简可靠起点，适用于快速摸清全局最大段分布：

SELECT owner, segment_name, segment_type, ROUND(SUM(bytes)/1024/1024) AS size_mbFROM dba_segmentsGROUP BY owner, segment_name, segment_typeORDER BY SUM(bytes) DESCFETCH FIRST 10 ROWS ONLY;

• FETCH FIRST 10 ROWS ONLY 语义清晰，比子查询套 ROWNUM 更安全，也避开别名歧义问题
• segment_type 列必须保留——看到 LOBSEGMENT 或 INDEX 要立刻警觉，它们常比对应 TABLE 段大数倍
• 如果 segment_name 是类似 SYS_LOB0000092320C00005$$ 这种，别猜，马上去查 DBA_LOBS

查到大段后，如何归因到具体业务表？

段名 ≠ 表名。DBA_SEGMENTS.segment_name 是物理存储名，DBA_LOBS.table_name 和 DBA_INDEXES.table_name 才是业务归属线索。

• 对 segment_type = 'LOBSEGMENT' 的记录：用 DBA_LOBS.segment_name 关联 DBA_SEGMENTS.segment_name，拿到 DBA_LOBS.owner 和 DBA_LOBS.table_name
• 对 segment_type = 'INDEX' 的记录：用 DBA_INDEXES.index_name 关联 DBA_SEGMENTS.segment_name，再取 DBA_INDEXES.table_owner 和 DBA_INDEXES.table_name
• 临时段（segment_type = 'TEMPORARY'）和 UNDO 段要单独盯——它们不归属任何用户表，但可能因长事务或大排序持续膨胀

表空间使用率不准？别信 DBA_FREE_SPACE

当表空间显示“剩余 500MB”，却反复报 ORA-01653 错误，大概率是 DBA_FREE_SPACE 被碎片欺骗了。它统计的是空闲块，不是可用连续空间。

• 真实使用率必须基于 DBA_SEGMENTS.bytes 计算：已分配空间 = 所有段字节数之和
• 执行这个语句看哪个表空间真告急：

  SELECT df.tablespace_name,       ROUND(SUM(df.bytes)/1024/1024, 2) AS total_mb,       ROUND(SUM(s.bytes)/1024/1024, 2) AS used_mb,       ROUND(SUM(s.bytes)/SUM(df.bytes)*100, 2) AS pct_usedFROM dba_data_files dfJOIN dba_segments s ON df.tablespace_name = s.tablespace_nameGROUP BY df.tablespace_nameORDER BY pct_used DESC;

• 若某表空间 pct_used > 95%，但 DBA_FREE_SPACE 显示还有几百 MB，优先考虑 SHRINK SPACE 或迁移对象，而不是盲目加数据文件

真正难的不是写出那条 SQL，而是看到 LOBSEGMENT 时愿意多花30秒查 DBA_LOBS，看到 TEMPORARY 段时愿意去翻 V$TEMPSEG_USAGE。空间问题从来不是单点故障，而是多个视图拼出来的完整链条。