DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Monday, November 3, 2014

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

Find the below commands to rebuild Index, Partitioned Index, LOB Index, IOT (Index Organized Table),

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;