Getting information about table size, lob segment and lob index segment size which belongs to table

set linesize 256
col owner for a30
col table_name for a30
col segment_name for a90
col LOB_SECURED for a11
col LOB_COMPRESSION for a15
 
with t_tab as (
select -- Information about table size
       t.OWNER,
       t.TABLE_NAME,
       t.COMPRESSION as TABLE_COMPRESSION,
       t.COMPRESS_FOR as TABLE_COMPRESS_FOR
  from DBA_TABLES t
 where 1 = 1
   and t.TABLE_NAME = NVL(UPPER('&s_table_name'), t.TABLE_NAME)
   and t.OWNER = NVL(UPPER('&s_owner'), t.OWNER)
   and t.OWNER not in ('SYS', 'SYSTEM', 'SYSMAN', 'CTXSYS', 'XDB')
)
select -- Information about table size
       t.OWNER,
       t.TABLE_NAME,
       t.TABLE_COMPRESSION,
       t.TABLE_COMPRESS_FOR,
       ts.SEGMENT_NAME as SEGMENT_NAME,
       cast(null as varchar2(30)) as LOB_SECURED,
       cast(null as varchar2(30)) as LOB_COMPRESSION,
       ROUND(ts.bytes/(1024*1024),2) SPACE_ALOCATED_MB
  from t_tab t
  join DBA_SEGMENTS ts
    on ts.SEGMENT_NAME = t.TABLE_NAME
 where 1 = 1
 union all
select -- Information about lob columns size for table
       t.OWNER,
       t.TABLE_NAME,
       t.TABLE_COMPRESSION,
       t.TABLE_COMPRESS_FOR,
       ls.segment_type || ' "' || dl.segment_name || '" for table "' || dl.table_name || '"' as SEGMENT_NAME,      
       dl.securefile as LOB_SECURED,
       dl.compression as LOB_COMPRESSION,
       ROUND(ls.bytes/(1024*1024),2) SPACE_ALOCATED_MB
  from t_tab t
  join DBA_LOBS dl
    on dl.table_name = t.TABLE_NAME
  join DBA_SEGMENTS ls
    on ls.SEGMENT_NAME = dl.segment_name       
 where 1 = 1
 union all
select -- Information about lob index for lob column
       t.OWNER,
       t.TABLE_NAME,
       t.TABLE_COMPRESSION,
       t.TABLE_COMPRESS_FOR,
       lis.segment_type || ' "' || dl.index_name || '" for LOBSEGMENT "' || dl.segment_name || '"' as SEGMENT_NAME,
       dl.securefile as LOB_SECURED,
       dl.compression as LOB_COMPRESSION,
       ROUND(lis.bytes/(1024*1024),2) SPACE_ALOCATED_MB
  from t_tab t
  join DBA_LOBS dl
    on dl.table_name = t.TABLE_NAME
  join DBA_SEGMENTS lis
    on lis.SEGMENT_NAME = dl.index_name       
 where 1 = 1  
 ORDER BY SPACE_ALOCATED_MB desc, OWNER, TABLE_NAME, SEGMENT_NAME
;