如何排查Oracle中占用大量表空间的Segment段

数据库空间告警，最直接的动作就是查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。空间问题从来不是单点故障，而是多个视图拼出来的完整链条。