Oracle数据库top10物理段

select owner, name, type, mega, tbs
  from (select owner,
               case
                 when segment_type = 'LOBSEGMENT' then
                  logical_name
                 else
                  segment_name
               end as name,
               segment_type as type,
               round(bytes / 1024 / 1024) as mega,
               tablespace_name as tbs
          from (select a.owner,
                       a.segment_name,
                       a.segment_type,
                       b.table_name || '.' || b.column_name as logical_name,
                       bytes,
                       a.tablespace_name
                  from dba_segments a, dba_lobs b
                 where a.owner = b.owner(+)
                   and a.segment_name = b.segment_name(+)
                   and a.bytes > 1020 * 1024 * 1024)
         order by bytes desc);

这个SQL有一个不足之处。在数据库中有很多分区表的话，每个分区很小则不能正确获得表的真实大小。因此，将分区段进行聚合，所在表空间就不管了，随意取一个。

select owner, name, type, mega, tbs
  from (select owner,
               case
                 when segment_type like 'LOB%' then
                  logical_name
                 else
                  segment_name
               end as name,
               segment_type as type,
               round(bytes / 1024 / 1024) as mega,
               tablespace_name as tbs
          from (select a.owner,
                       a.segment_name,
                       a.segment_type,
                       b.table_name || '.' || b.column_name as logical_name,
                       bytes,
                       a.tablespace_name
                  from (select owner,
                               segment_name,
                               segment_type,
                               sum(bytes) as bytes,
                               max(tablespace_name) as tablespace_name
                          from dba_segments
                         group by owner, segment_name, segment_type) a,
                       dba_lobs b
                 where a.owner = b.owner(+)
                   and a.segment_name = b.segment_name(+)
                   and a.bytes > 1024 * 1024 * 1024)
         order by bytes desc);