What if the partition of an ORA-01502 index or such index is unavailable

This article shows you how to do when ORA-01502 index or partition of such index is unavailable. The content is concise and easy to understand. It will definitely make your eyes shine. I hope you can gain something through the detailed introduction of this article.

ORA-01502 Index xx or partition of such index is unavailable

After the index is invalid, it is impossible to insert data into the table

Cause: This problem occurs, someone may have moved the table, or disabled the index.

1. After the command alter table xxxxxx move tablespace xxxxxxx, the index becomes invalid.

2. alter index_name unusable The command invalidates the index.

After deleting the data, move reclaim the table space causing the table index to become invalid.

alter table message move;

1. Index failure rebuild index resolution:

--Index search invalid

select index_name,index_type,tablespace_name,table_type,status from user_indexes where index_name='PK_MESSAGE';

--Set not to skip invalid index

alter session set skip_unusable_indexes=false;

--rebuild index

alter index PK_MESSAGE rebuild;

2. If it is a partitioned index, only the failed partition needs to be rebuilt.

alter index index_name rebuild partition partition_name (online);

or alter index_name rebuild partition_name ;

[Summary]

1. After moving the table space of the table, the index based on the table will automatically fail UNUSABLE; at this time, when accessing or operating the table, ORA-01502 exception will be reported;

2. For ordinary indexes, ORA-01502 exception can be solved by skipping invalid indexes or rebuilding indexes;

For unique indexes, ORA-01502 exception can only be resolved by rebuilding the index.

There are 4 types of index states:

N/A indicates that the partition index needs to look up user_ind_partitions or user_ind_partitions to determine whether each partition is available;

VAILD indicates that this index is available;

UNUSABLE indicates that the index is unavailable;

USABLE indicates that partitions of this index are available.

4. Query the status of the current index: select distinct status from user_indexes;

5. Select index_name from user_indexes where status 'VALID';

6. Batch rebuild: select 'alter index '|| index_name|| ' rebuild online;' from user_indexes where status 'VALID' and index_name not like'%$$';

The above is ORA-01502 index or partition of such index is unavailable. What should I do? Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserves, please pay attention to the industry information channel.