Schema Space Usage Report

SQL:
set linesize 110
set pagesize 0
set newpage 0
col dummy noprint
col table_index heading 'Table/Index' format a60
col seg_type    heading 'Type'        format a15
col num_rows    heading '# Rows'      format 999,999,999,999
col size_mb     heading 'Size (MB)'   format 999,999,999
compute sum of size_mb on dummy
compute sum of size_mb on report
break on dummy skip 1 on report
select nvl(i.table_name,t.table_name) dummy,
       nvl(i.table_name,t.table_name)||' '||i.index_name table_index,
       initcap(s.segment_type) seg_type,
       t.num_rows,
       round(sum(s.bytes)/1024/1024) size_mb
  from dba_segments s,
       dba_tables t,
       dba_indexes i
 where s.owner = '&Schema'
   and s.segment_name = t.table_name (+)
   and s.segment_name = i.index_name (+)
   and s.segment_type not in ('LOBSEGMENT','LOBINDEX')
 group by nvl(i.table_name,t.table_name),
          nvl(i.table_name,t.table_name)||' '||i.index_name,
          s.segment_type,
          t.num_rows
-- having round(sum(s.bytes)/1024/1024) > 10
 order by nvl(i.table_name,t.table_name), s.segment_type desc
/
exit
/