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;