Wednesday, 20 January 2016

Oracle: Index: Unusable Rebuild ORA-01502

How to deal with errors reporting Unusable indexes(ORA-01502). 

Must resolve as soon as possible to avoid query optimizer going berserk and cause havoc on DB with un-optimized plans:

For Non-partiton indexes:

Find:

 select owner, index_name, tablespace_name from dba_indexes where status = 'UNUSABLE';

Generate fix:

select 'alter index '||index_name||' rebuild tablespace '||tablespace_name ||';' from dba_indexes where status = 'UNUSABLE';




For Partitioned indexes:

Find:

select index_owner, index_name, partition_name, tablespace_name from dba_ind_PARTITIONS where status = 'UNUSABLE';

Generate Fix:

select 'alter index '||index_name ||' rebuild partition '||PARTITION_NAME||' TABLESPACE '||tablespace_name ||';' from dba_ind_partitions where status = 'UNUSABLE';


Then just run the fix statements.


No comments:

Post a Comment