Cause and solution of ORA-01502 state unusable error (1)

After receiving a notification from developers and business personnel, the following error occurs:

13:59:02, 721 [COM. aspire. Common. Dao. oamuserdao]--214: Select Error
Java. SQL. sqlexception: ORA-01502 state

This error is caused by index failure. After the index is rebuilt, the problem is solved.

To find out why the index fails and how to solve it, let's test:

First, create a normal test table (non-partition table ):
SQL> Create Table T (a number );

Table created.

SQL> select tablespace_name from user_segments where segment_name = 'T ';

Tablespace_name
------------------------------
Data_dynamic

SQL>

Then, we create a common index

SQL> Create index idxt on T ();

Index created.

SQL> insert into T values (10 );

1 row created.

SQL> set linesize 200
SQL> select index_name, index_type, tablespace_name, table_type, status from user_indexes where index_name = 'idxt ';

Index_name index_type tablespace_name table_type status
----------------------------------------------------------------------------------------------------------
Idxt normal data_dynamic table valid

SQL>

The simulated index is invalid:
SQL> ALTER TABLE t move tablespace tools
2/

Table altered.

SQL> select index_name, index_type, tablespace_name, table_type, status from user_indexes where index_name = 'idxt ';

Index_name index_type tablespace_name table_type status
----------------------------------------------------------------------------------------------------------
Idxt normal data_dynamic table unusable

SQL>
We can see that when the alter table xxxxxx move tablespace xxxxxxx command is used, the index will become invalid.

Of course, as a test, you can also use the alter index idxt unusable command to invalidate the index, for example:
SQL> alter index idxt unusable;

Index altered.

SQL>

In this case, we can insert data into the table to see what the situation is:
SQL> insert into T values (11 );
Insert into T values (11)
*
Error at line 1:
ORA-01502: Index 'misc. idxt 'or partition of such index is in unusable state.

SQL>
As we can see, there is a common "ORA-01502: Index 'xxxxxxxx' or partition of such index is in unusable state" error.

Check the index status and we will notice that the index is "unusable.
SQL> select index_name, index_type, tablespace_name, table_type, status from user_indexes where index_name = 'idxt ';

Index_name index_type tablespace_name table_type status
----------------------------------------------------------------------------------------------------------
Idxt normal data_dynamic table unusable

SQL>

For different indexes (non-unique indexes) in normal tables, we have two solutions to this problem.
Method 1: Set skip_unusable_indexes to true;
SQL> alter session set skip_unusable_indexes = true;

Session altered.

SQL> insert into T values (11 );

1 row created.

SQL> commit;

Commit complete.

SQL> select * from T;

A
----------
1
2
3
4
5
10
11

7 rows selected.

SQL> select index_name, index_type, tablespace_name, table_type, status from user_indexes where index_name = 'idxt ';

Index_name index_type tablespace_name table_type status
----------------------------------------------------------------------------------------------------------
Idxt normal data_dynamic table unusable

SQL>
Now we can see that although the index status is still "unusable", by setting "alter session set skip_unusable_indexes = true ;",
We can already access this table, but please note that in this case, this index is unavailable, that is, the optimizer does not consider this when considering whether to use the index.

Method 2: solve this problem through common reasons
First, set "skip_unusable_indexes = false", that is, do not skip the invalid index.
SQL> alter session set skip_unusable_indexes = false;

Session altered.

SQL>

Then re-create the invalid index.
SQL> alter index idxt rebuild;

Index altered.

SQL> select index_name, index_type, tablespace_name, table_type, status from user_indexes where index_name = 'idxt ';

Index_name index_type tablespace_name table_type status
----------------------------------------------------------------------------------------------------------
Idxt normal data_dynamic table valid

SQL>
We can see that after the index is re-built, the index status will be normal.

Insert data now to see if it is normal:
SQL> insert into T values (12 );

1 row created.

SQL> commit;

Commit complete.

SQL>
It seems that re-indexing is a thorough solution to such problems.