Com a view DBA_SEGMENTS do usuário DBA, é possível consultar os maiores objetos e tabelas no banco.
Comando SQL para consultar todos objetos;
select
SEGMENT_NAME,
SEGMENT_TYPE,
BYTES/1024/1024 mb,
BYTES/1024/1024/1024 GB,
tablespace_name
from
dba_segments
order by mb desc
Comando SQL para consultar as 5 maiores tabelas;
Você pode alterar a busca conforme necessitar, a partir da columa segment_type, poderá consultar apenas as tabelas.
select * from (
select
owner,
SEGMENT_NAME,
SEGMENT_TYPE,
bytes/1024/1024 mb,
bytes/1024/1024/1024 GB,
tablespace_name
from
dba_segments
where segment_type = 'TABLE'
order by mb desc
)
where rownum <= 5;
Exemplo de consulta buscando os objetos de um tablespace (tablespace_name).
select * from (
select
owner,
SEGMENT_NAME,
SEGMENT_TYPE,
sum(bytes/1024/1024) mb,
sum(bytes/1024/1024/1024) GB,
tablespace_name
from
dba_segments
where segment_type = 'TABLE'
and tablespace_name = 'TEMP'
group by segment_type, owner, SEGMENT_NAME, tablespace_name
order by mb desc
)
where rownum <= 5;