Oracle invalid index repair

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

Reason: this problem occurs, someone may move over the table, or disable over the index.

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

2. Alter index index_name unusable, command invalidates the index.

Solution:

1. Rebuilding the index is the complete way to solve this kind of problem.

Alter index index_name rebuild (online)

Or alter index index_name rebuild.

two。 In the case of a partition index, only the invalid partition needs to be rebuilt.

Alter index index_name rebuild partition partition_name (online)

Or alter index index_name rebuild partition partition_name.

3. Or change the name of the current index.

Description:

1. Alter session set skip_unusable_indexes=true; can skip invalid indexes at the session level to make queries.

two。 The partitioned index should apply user_ind_partitions.

3. There are 4 kinds of status:

NCMA indicates that this partition index needs to check user_ind_partitions or user_ind_subpartitions to determine whether each partition is available.

VAILD indicates that this index is available

UNUSABLE indicates that this index is not available

USABLE indicates that the partition of this index is available.

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

5. It is invalid to query that index: select index_name from user_indexes where status' VALID'

6. For more information, please refer to http://www.sudu.cn/info/html/edu/20071225/20526.html.

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