ORA-01502 index is in unusable state

SQL> show parameter skip

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

skip_unusable_indexes boolean TRUE

默认情况下skip_unusable_indexes=true

SQL> create table tt (id int,name varchar2(100))

2 ;

Table created.

SQL> create table tt1 (id int,name varchar2(100))

2 ;

Table created.

SQL> create unique index tt_index on tt (id);

Index created.

创建 unique index

SQL> alter table tt add primary key (id) using index;

Table altered.

SQL> create index tt1_index on tt1(id);

Index created.

创建 normal index

SQL> select index_name,status from user_indexes;

INDEX_NAME STATUS

------------------------------ --------

TT1_INDEX VALID

TT_INDEX VALID

期间有向tt,tt1表insert 了一笔数据。

SQL> alter table tt move tablespace test;

Table altered.

SQL> alter table tt1 move tablespace test;

Table altered.

SQL> select index_name,status from user_indexes;

INDEX_NAME STATUS

------------------------------ --------

TT1_INDEX UNUSABLE

TT_INDEX UNUSABLE

SQL> insert into tt values(1,'a');

insert into tt values(1,'a')

*

ERROR at line 1:

ORA-01502: index 'TEST.TT_INDEX' or partition of such index is in unusable

state

unique index出现了此错误

SQL> insert into tt1 values(1,'a');

1 row created.

SQL> commit;

normal index没有出现错误

Commit complete.

SQL> select * from tt;

ID NAME

---------- ----------

1 b

SQL> select * from tt1;

ID NAME

---------- ----------

1 b

1 a

SQL> alter index tt1_index rebuild;

Index altered.

SQL> alter index tt_index rebuild;

Index altered.

SQL> select index_name,status from user_indexes;

INDEX_NAME STATUS

------------------------------ --------

TT1_INDEX VALID

TT_INDEX VALID

SQL> insert into tt values(1,'a');

insert into tt values(1,'a')

*

ERROR at line 1:

ORA-00001: unique constraint (TEST.SYS_C003773) violated

如果index为valid的话，insert相同的数据，会报ora-00001的错误。

当把index变为unusable，truncate table之后，index会变为valid

SQL> alter index tt_index unusable;

Index altered.

SQL> select index_name,status from user_indexes;

INDEX_NAME STATUS

------------------------------ --------

TT1_INDEX VALID

TT_INDEX UNUSABLE

SQL> truncate table tt;

Table truncated.

SQL> select index_name,status from user_indexes;

INDEX_NAME STATUS

------------------------------ --------

TT1_INDEX VALID

TT_INDEX VALID

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24237320/viewspace-1059226/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24237320/viewspace-1059226/