Find index larger than their corresponding tables in oracle

Top 5 Indexes bigger than their corresponding Tables

Find the indexes largest than table and need to rebuild it.

set head on
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
(select owner,segment_name, round(sum(bytes/(1024*1024*1024)),2) TAB_GB
from dba_segments where segment_type=apos;TABLEapos;
and owner not in (apos;SYSapos;,apos;SYSTEMapos;)
group by owner,segment_name) a,
(select s.owner,i.table_name, i.index_name, round(sum(bytes/(1024*1024*1024)),2) IND_GB
from dba_indexes i, dba_segments s
where s.segment_type=apos;INDEXapos;
and s.segment_name=i.index_name
and s.owner not in (apos;SYSapos;,apos;SYSTEMapos;)
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;
set head off


For rebuild index:

alter index index_name rebuild ;



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s