Commands to rebuild Index, Partitioned Index, LOB Index, IOT (Index Organized Table)

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;