SQL> CREATE TABLE T (A Number);
Table created.
Now we create a unique cable to see: SQL> CREATE UNIQUE INDEX IDX_T ON T (A);
Index created.
SQL> SELECT INDEX_NAME, INDEX_TYPE, TABLESPACE_NAME, TABLE_TYPE, STATUS from user_indexes where index_name = 't';
No rows selected
SQL> SELECT INDEX_NAME, INDEX_TYPE, TABLESPACE_NAME, TABLE_TYPE, STATUS from user_indexes where index_name = 'idx_t';
INDEX_NAME INDEX_TYPE TABLESPACE_NAME TABLE_TYPE STATUS ------------------------------------------------------------------------------------------- ------------ ------------------------------------ --- -------- IDX_T NORMAL DATA_DYNAMIC TABLE VALID
SQL> INSERT INTO T VALUES (1);
1 row created.
SQL> commit;
COMMIT COMPLETE.
Hand modify the index to UNUSABLE status (analog index failure): SQL> ALTER INDEX IDX_T UNUSABLE;
Index altered.
SQL> SELECT INDEX_NAME, INDEX_TYPE, TABLESPACE_NAME, TABLE_TYPE, STATUS from user_indexes where index_name = 'idx_t';
INDEX_NAME INDEX_TYPE TABLESPACE_NAME TABLE_TYPE STATUS ------------------------------------------------------------------------------------------- ------------ ------------------------------------ --- -------- Idx_t Normal Data_Dynamic Table Unusable
We see this, it is no longer possible to insert the data in the table: SQL> Insert Into T Values (2); Insert Into T Values (2) * Error At Line 1: ORA-01502: Index 'misc.idx_t' or partition Of Such Index Is in Unusable State
First, we solve the problem by rebuild index: SQL> ALTER INDEX IDX_T REBUILD;
Index altered.
SQL> select index_name, index_type, tablespace_name, table_type, status from user_indexes where index_name = 'IDX_T'; INDEX_NAME INDEX_TYPE TABLESPACE_NAME TABLE_TYPE STATUS ------------------------ ------ ------------------------------- ------------------------ --------- iDX_t Normal Data_Dynamic Table Valid
SQL> INSERT INTO T VALUES (2);
1 row created.
SQL> commit;
COMMIT COMPLETE.
SQL>
Now we will simulate index (UNUSABLE state): SQL> ALTER INDEX IDX_T UNUSABLE;
Index altered.
SQL> SELECT INDEX_NAME, INDEX_TYPE, TABLESPACE_NAME, TABLE_TYPE, STATUS from user_indexes where index_name = 'idx_t';
INDEX_NAME INDEX_TYPE TABLESPACE_NAME TABLE_TYPE STATUS ------------------------------------------------------------------------------------------- ------------ ------------------------------------ --- -------- Idx_t Normal Data_Dynamic Table Unusable
SQL> INSERT INTO T VALUES (3); Insert Into T Values (3) * Error At Line 1: ORA-01502: Index 'Misc.IDX_T' OR Partition of Such INDEX IS in Unusable State
Then, check if you can solve the problem by setting the parameter skip_unusable_indexes = true: SQL> ALTER Session Set Skip_unusable_indexes = true;
Session altered.
SQL> INSERT INTO T VALUES (3); Insert Into T Values (3) * Error At Line 1: ORA-01502: Index 'Misc.IDX_T' OR Partition of Such INDEX IS in Unusable State
SQL> SELECT INDEX_NAME, INDEX_TYPE, TABLESPACE_NAME, TABLE_TYPE, STATUS from user_indexes where index_name = 'idx_t';
INDEX_NAME INDEX_TYPE TABLESPACE_NAME TABLE_TYPE STATUS ------------------------------------------------------------------------------------------- ------------ ------------------------------------ --- -------- IDX_T NORMAL DATA_DYNAMIC TABLE UNUSABLESQL> ALTER INDEX IDX_T REBUILD;
Index altered.
SQL> SELECT INDEX_NAME, INDEX_TYPE, TABLESPACE_NAME, TABLE_TYPE, STATUS from user_indexes where index_name = 'idx_t';
INDEX_NAME INDEX_TYPE TABLESPACE_NAME TABLE_TYPE STATUS ------------------------------------------------------------------------------------------- ------------ ------------------------------------ --- -------- IDX_T NORMAL DATA_DYNAMIC TABLE VALID
SQL> INSERT INTO T VALUES (3);
1 row created.
SQL> commit;
COMMIT COMPLETE.
SQL> Obviously, for UNIQUE INDEX, you can't solve the problem by simple setting parameters, to solve the problem of Unique Index failure, can only be achieved by reconstruction.
