onesqltocalculatetheschema'stablecapacity

SELECT owner,
 table_name,
 TRUNC(SUM(bytes)/(1024*1024)) MB
FROM
 (SELECT segment_name table_name,
 owner,
 bytes
 FROM dba_segments
 WHERE segment_type = 'TABLE'
 UNION ALL
 SELECT i.table_name,
 i.owner,
 s.bytes
 FROM dba_indexes i,
 dba_segments s
 WHERE s.segment_name = i.index_name
 AND s.owner = i.owner
 AND s.segment_type = 'INDEX'
 UNION ALL
 SELECT l.table_name,
 l.owner,
 s.bytes
 FROM dba_lobs l,
 dba_segments s
 WHERE s.segment_name = l.segment_name
 AND s.owner = l.owner
 AND s.segment_type = 'LOBSEGMENT'
 UNION ALL
 SELECT l.table_name,
 l.owner,
 s.bytes
 FROM dba_lobs l,
 dba_segments s
 WHERE s.segment_name = l.index_name
 AND s.owner = l.owner
 AND s.segment_type = 'LOBINDEX'
 )
GROUP BY table_name,
 owner
ORDER BY SUM(bytes) DESC ;