-- Size Analysis of User Tables and Materialized Views

SELECT
 SUBSTR(DECODE(GROUPING(t.table_name), 0, t.table_name,
  'Total ('||TO_CHAR(SYSDATE,'mm/dd/yyyy hh24:mi:ss')||'): '),1,40), 
 ROUND(SUM(s.bytes)/16384,3)  Blocks,
 ROUND(SUM(s.bytes)/1024/1024,3) size_mb
FROM user_segments s,
     user_tables t
WHERE  s.segment_name = t.table_name 
GROUP BY ROLLUP(t.table_name)
;

-- Size Analysis of a Table or Materialized View

SELECT  MIN(t.table_name)  table_name,
 ROUND(SUM(s.bytes)/16384,3)  Blocks,
 ROUND(SUM(s.bytes)/1024/1024,3) size_mb
FROM user_segments s,
     user_tables t
WHERE  s.segment_name = t.table_name 
AND    t.table_name = UPPER('&tname')
;

-- Size Analysis of DBA Tables and Materialized Views

SELECT
 t.table_name,
 s.tablespace_name,
 ROUND(SUM(s.bytes)/16384,3)  Blocks,
 ROUND(SUM(s.bytes)/1024/1024,3) size_mb
FROM dba_segments s,
     dba_tables t
WHERE  s.segment_name = t.table_name 
GROUP BY t.table_name, s.tablespace_name
;