Find the list of top index size corresponding to a table.

SELECT idx.table_name,bytes/1024/1024/1024
FROM dba_segments seg,
dba_indexes idx
where idx.table_name='&TABLE_NAME'
AND idx.index_name = seg.segment_name
GROUP BY idx.table_name order by 1;

Find total index size of respective tables in a schema.

SELECT idx.table_name, SUM(bytes/1024/1024/1024)
FROM dba_segments seg,
dba_indexes idx
WHERE idx.table_owner = 'HR'
AND idx.owner = seg.owner
AND idx.index_name = seg.segment_name
GROUP BY idx.table_name order by 1

Find the list of top index size in a schema

SELECT idx.table_name,idx.index_name, SUM(bytes/1024/1024/1024)
FROM dba_segments seg,
dba_indexes idx
WHERE idx.table_owner = 'HR'
AND idx.owner = seg.owner
AND idx.index_name = seg.segment_name
GROUP BY idx.table_name, idx.index_name order by 3 desc;

Find the list of index which is bigger than table size.

set lines 200
col "Table" format a30
col "Index" format a30
col "Owner" format a20
col tab_gb format 999999.99
col ind_gb format 999999.99
select * from (
select a.owner, a.segment_name "Table", a.tab_gb, b.index_name "Index", b.ind_gb
from
(select owner,segment_name, round(sum(bytes/(102410241024)),2) TAB_GB
from dba_segments where segment_type='TABLE'
and owner not in ('SYS','SYSTEM')
group by owner,segment_name) a,
(select s.owner,i.table_name, i.index_name, round(sum(bytes/(102410241024)),2) IND_GB
from dba_indexes i, dba_segments s
where s.segment_type='INDEX'
and s.segment_name=i.index_name
and s.owner not in ('SYS','SYSTEM')
group by s.owner,i.table_name, i.index_name) b
where a.owner=b.owner
and a.segment_name=b.table_name
and a.tab_gb < b.ind_gb
order by b.ind_gb desc
) where rownum<6;