Received a notice from developers and business people, saying that a landing page cannot be used, 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 solved after the index failure, the problem is resolved.
In order to figure out why the index will fail, and how to solve it, we have a test:
First we create an ordinary 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 normal index
SQL> CREATE INDEX Idxt ON T (a);
Index created.
SQL> INSERT INTO T VALUES (10);
1 row created.
SQL> SET LINESIZE 200SQL> 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 simulation 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 see that when using similar ALTER TABLE XXXXXXXM commands, the index will be invalid. Of course, as a test, the alter index idxt unusable; command can make index, for example: SQL> ALTER INDEX IdXT UNUSABLE
Index altered.
SQL>
In this case, we insert data into the table. What is the situation: SQL> INSERT INTO T VALUES (11); Insert Into T Values (11) * Error At line 1: ORA-01502: Index 'misc.idxt 'or partition of self index is in unusable stat
SQL> We see that there is a common "ORA-01502: index 'xxxxxxxx' or partition of claim index is in unusable stat" error.
Check the index state, we will 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>
For different indexes in the normal table (non-unique index), we have two ways to solve this problem. Method 1: 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 Unusables QL> Now we see, though the status of this index is still "unusable", but by setting "Alter Session Set Skip_unusable_indexes = true;", we can have access to this Table, but please note that this index is unavailable, that is, the optimizer is not considered when considering whether it is to use indexes.
Method 2: Thoroughly solve this problem by common, first set "Skip_unusable_indexes = false", which is not skipping index SQL> ALTER session set skip_unusable_indexes = false;
Session altered.
SQL>
Then rebuild this failure 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 the reconstruction index, the state of the index is normal.
Insert data now, see is normal: SQL> Insert Into T Values (12);
1 row created.
SQL> commit;
COMMIT COMPLETE.
SQL> It seems that rebuilding an index is a thorough approach to solving such problems.
