Wednesday, May 6, 2015

Partition table/index rebuild steps

Use below steps to generate rebuild scripts for table and indexes and execute o/p on database. 

select  'alter table '||OWNER||'.'||SEGMENT_NAME||' '||'move partition '||PARTITION_NAME ||';' from dba_segments where OWNER='ADMUSER' AND SEGMENT_NAME='TEST1';

select 'alter index '||index_owner||'.'||index_name||' rebuild partition '|| PARTITION_NAME ||';' from dba_ind_partitions   where index_name in (select  index_name from dba_indexes where table_name='TEST1')



Verify the index status using below scripts

select INDEX_OWNER , INDEX_NAME , STATUS from dba_ind_partitions where STATUS='UNUSABLE'
 select index_name , status , owner from dba_indexes where status='UNUSABLE'

No comments:

Post a Comment