Shulou(Shulou.com)05/31 Report--

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.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.