Introduction
Here are the Commands to rebuild Index, Partitioned Index, LOB Index, IOT (Index Organized Table)
Posted by Sakthi Sethu Perumal
To rebuild the index:
set pagesize 1000
SELECT ‘ALTER INDEX ‘ || OWNER ||’.’||index_name ||’ REBUILD tablespace TARGET_TABLESPACE PARALLEL 8 ONLINE COMPUTE STATISTICS;’
from dba_indexes where tablespace_name=’SOURCE_TABLESPACE’;
To rebuild the partitioned index:
set pagesize 1000
SELECT ‘ALTER INDEX ‘ || INDEX_OWNER ||’.’||index_name ||’ REBUILD PARTITION ‘||PARTITION_NAME||’ tablespace TARGET_TABLESPACE Parallel;’ from dba_ind_partitions where tablespace_name=’SOURCE_TABLESPACE’;
To rebuild the LOB index:
select ‘alter table ‘||owner||’.’||table_name||’ move tablespace TARGET_TABLESPACE ‘||chr(10)||
‘LOB (‘||'”‘||column_name||'”‘||’) store as ‘||segment_name||chr(10)||
‘(tablespace TARGET_TABLESPACE);’
from dba_lobs
where owner in (‘<owner_name>’)
and tablespace_name=’SOURCE_TABLESPACE’;
To rebuild the index organized table index (IOT):
1. Find the table name for IOT index
select owner,table_name,index_name,tablespace_name from dba_indexes where index_name=’IOT_INDEX_NAME’;
2. Move that table to reubuild index
alter table OWNER.TABLE_NAME move tablespace TARGET_TABLESPACE;
