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@]

ORA-01502 index is in unusable state

相關文章