Measure Table Size in ORACLE

Sometimes it is required to measure how much space a tables occupy in Oracle. The size sum-up table and its co-related indexes, partitions, lobs, lob partitions. here are two SQL scripts, first one listed all tables own by a specific user and second one  include tablespace too.

SELECT segment_name, sum(size_mb) size_mb
FROM (
SELECT CASE WHEN x.segment_type in ('LOBSEGMENT','LOB PARTITION') THEN 
  (SELECT table_name FROM dba_lobs y WHERE y.segment_name= x.segment_name)
  WHEN x.segment_type='LOBINDEX' THEN 
  (SELECT table_name FROM dba_lobs y WHERE y.index_name = x.segment_name)
  WHEN x.segment_type in('INDEX','INDEX PARTITION','INDEX SUBPARTITION') THEN 
  (SELECT y.table_name FROM dba_indexes y WHERE y.index_name = x.segment_name)
  WHEN x.segment_type in ('TABLE SUBPARTITION', 'TABLE PARTITION','TABLE')
  THEN x.segment_name END segment_name
,round(sum(x.bytes)/(1024*1024),2) size_mb 
FROM dba_segments x 
WHERE x.owner ='SCOTT'
GROUP BY x.segment_name, x.segment_type
)
GROUP BY segment_name
ORDER BY size_mb DESC;


SELECT segment_name, tablespace_name, sum(size_mb) size_mb
FROM (
SELECT CASE WHEN x.segment_type in ('LOBSEGMENT','LOB PARTITION') THEN 
  (SELECT table_name FROM dba_lobs y WHERE y.segment_name= x.segment_name)
  WHEN x.segment_type='LOBINDEX' THEN 
  (SELECT table_name FROM dba_lobs y WHERE y.index_name = x.segment_name)
  WHEN x.segment_type in('INDEX','INDEX PARTITION','INDEX SUBPARTITION') THEN 
  (SELECT y.table_name FROM dba_indexes y WHERE y.index_name = x.segment_name)
  WHEN x.segment_type in ('TABLE SUBPARTITION', 'TABLE PARTITION','TABLE')
  THEN x.segment_name END segment_name
, x.tablespace_name
, round(sum(x.bytes)/(1024*1024),2) size_mb 
FROM dba_segments x 
WHERE x.owner ='SCOTT'
GROUP BY x.segment_name, x.tablespace_name,x.segment_type
)
GROUP BY segment_name, tablespace_name
ORDER BY size_mb DESC;