Causes and solutions of ORA-01502 State unusable errors (i)

Error | resolution
Received a notice from developers and business personnel that a landing page could not be used, the error:

2005-01-31 13:59:02,721 [com.aspire.common.dao.oamuserdao]- -214:select Error
java.sql.sqlexception:ora-01502 State

This error is caused by index invalidation, and the problem is solved after rebuilding the index.

To figure out why the index will fail and how to solve it, let's do a test:

First we create a normal test table (not a partitioned 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 normal index

Sql> CREATE index IDXT on t (a);

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 impersonation index is invalidated:
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 see that the index is invalidated when the xxxxxxx command is used like ALTER TABLE XXXXXX move tablespace.

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 insert data into the table to see what happens:
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'll notice that the index is already "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>

There are two ways to solve this problem for different indexes (not unique indexes) in a common table.
Method one: Set skip_unusable_indexes=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 see that the state of the index is still "unusable" but, by setting "alter session set Skip_unusable_indexes=true;",
We already have access to this table, but note that in this case the index is not available, which means that the optimizer does not consider this when considering whether to use the index.

Method 2: Through common so thoroughly solve this problem
First, set "Skip_unusable_indexes=false", that is, not skipping the fail index
Sql> alter session set Skip_unusable_indexes=false;

Session altered.

Sql>

And then rebuild this failed 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>
After we see rebuilding the index, the status of the index is normal.

Now insert the data to see if it is normal:
Sql> INSERT INTO T values (12);

1 row created.

Sql> commit;

Commit complete.

Sql>
It seems that rebuilding the index is the definitive way to solve such problems.