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:

-- For offline rebuild
alter index index_name rebuild;

--For online rebuild
alter index index_name rebuild online;

--Rebuild in new tablespace
alter index index_name rebuild tablespace newtablespace;

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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.