Show the ten largest objects in the database

col owner format a15
col segment_name format a30
col segment_type format a15
col mb format 999,999,999
select  owner
, segment_name
, segment_type
, mb
from  (
  select  owner
  , segment_name
  , segment_type
  , bytes / 1024 / 1024 "MB"
  from  dba_segments
  order by bytes desc
  )
where rownum < 11

sample output

OWNER  SEGMENT_NAME  SEGMENT_TYPE  MB
----------- --------------------------- ------------------------  -----
A               emp                           TABLE                      90.025
A               emp_pk                     INDEX                      10.50
A               dept                            TABLE                     5.251