Tuesday, January 13, 2015

Index size is more than table size to rebuild

select 'alter index '||a.index_owner||'.'||a.index_name||' rebuild online;' from
(select i.OWNER "INDEX_OWNER",i.INDEX_NAME,i.table_name,s.bytes/1024/1024 "INDEX_SIZE"
from dba_indexes i,dba_segments s
where i.index_name=s.SEGMENT_NAME and i.owner=s.owner and s.bytes/1024/1024 > 20) a,
(select t.OWNER "TABLE_OWNER",t.table_name,s.bytes/1024/1024 "TABLE_SIZE"
from dba_tables t,dba_segments s where t.table_name=s.SEGMENT_NAME and t.owner=s.owner and s.bytes/1024/1024 > 20) b
where a.index_owner=b.table_owner and a.table_name=b.table_name and a.index_size > b.table_size;

No comments:

Post a Comment