oracle数据库物理 过大,Oracle数据库top10物理段-Oracle

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