Table and Index Size...

SQL to list out Tables and Indexes size for a given Schema:

--This Lists out Index size for all indexes in the table:

select ind.table_name, round(tbl.table_size,2) "Table Size (GB)", round(ind.index_size,2) "Indexes Size (GB)", 

       round((tbl.table_size + ind.index_size), 2) "Total Size (GB)"

  from (select segment_name, segment_type, sum(bytes / 1024) / 1024 / 1024 table_size

          from dba_segments

         where segment_type in ('TABLE') 

           and owner = '&Schema_Owner'

        group by segment_name, segment_type

       ) tbl,

       (select db.table_name, da.segment_type, sum(bytes / 1024) / 1024 / 1024 index_size

          from dba_segments da, dba_indexes db

         where da.segment_type in ('INDEX')

           and da.owner = '&Schema_Owner'

           and da.tablespace_name = db.tablespace_name

           and da.segment_name = db.index_name

          group by da.segment_type, db.table_name

       ) ind

 where tbl.segment_name = ind.table_name 

 order by tbl.table_size + ind.index_size desc;

--with Tablespace Info:

select ind.table_name, tbl.tablespace_name "Table TS", round(tbl.table_size,2) "Table Size (GB)", 

       ind.tablespace_name "Index TS", round(ind.index_size,2) "Indexes Size (GB)", 

       round((tbl.table_size + ind.index_size), 2) "Total Size (GB)"

  from (select segment_name, segment_type, tablespace_name, sum(bytes / 1024) / 1024 / 1024 table_size

          from dba_segments 

         where segment_type in ('TABLE') 

           and owner = '&Schema_Owner'

        group by segment_name, segment_type, tablespace_name

       ) tbl,

       (select db.table_name, da.segment_type, da.tablespace_name, sum(bytes / 1024) / 1024 / 1024 index_size

          from dba_segments da, dba_indexes db

         where da.segment_type in ('INDEX')

           and da.owner = '&Schema_Owner'

           and da.tablespace_name = db.tablespace_name

           and da.segment_name = db.index_name

          group by da.segment_type, db.table_name,da.tablespace_name

       ) ind

 where tbl.segment_name = ind.table_name 

 order by tbl.table_size + ind.index_size desc;

--This Lists out Individual Index Size within the Table:

select ind.table_name, ind.index_name, round(tbl.table_size,2) "Table Size (GB)", sum(ind.index_size) "Indexes Size (GB)" 

       --sum(tbl.table_size) + sum(ind.index_size) "Total Size (GB)"

  from (select segment_name, segment_type, sum(bytes / 1024) / 1024 / 1024 table_size

          from dba_segments

         where segment_type in ('TABLE') 

           and owner = '&Schema_Owner'

        group by segment_name, segment_type

       ) tbl,

       (select db.table_name, db.index_name, da.segment_type, sum(bytes / 1024) / 1024 / 1024 index_size

          from dba_segments da, dba_indexes db

         where da.segment_type in ('INDEX')

           and da.owner = '&Schema_Owner'

           and da.tablespace_name = db.tablespace_name

           and da.segment_name = db.index_name

          group by da.segment_type, db.table_name, db.index_name

       ) ind

 where tbl.segment_name = ind.table_name 

 group by rollup (ind.table_name, ind.index_name, tbl.table_size)

 order by 1