Top 10 physical segments of Oracle databases

Top 10 physical segments of Oracle databases

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);

 

This SQL statement has one disadvantage. If there are many partition tables in the database, the actual table size cannot be obtained if each partition is small. Therefore, partition segments are aggregated, And the tablespace in the partition does not matter.

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);