Friday 22 April 2022

Make Unusable and Invalid Index to VALID in oracle database

Make Unusable and Invalid Index to VALID in oracle database with below commands

Indexes:
SELECT 'alter index '||owner||'.'||index_name||' rebuild tablespace '||tablespace_name ||' ONLINE PARALLEL 8;'  FROM   dba_indexes  WHERE  status in('INVALID','UNUSABLE');

Index partitions:
SELECT 'alter index '||index_owner||'.'||index_name ||' rebuild partition '||PARTITION_NAME||' TABLESPACE '||tablespace_name ||' ONLINE PARALLEL 8;'  FROM   dba_ind_partitions  WHERE status in('INVALID','UNUSABLE');

Index subpartitions:
SELECT 'alter index '||index_owner||'.'||index_name ||' rebuild subpartition '||SUBPARTITION_NAME||' TABLESPACE '||tablespace_name ||' ONLINE PARALLEL 8;'  FROM   dba_ind_subpartitions WHERE  status in('INVALID','UNUSABLE');


No comments:

Post a Comment