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.
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