unique index unusable

/*
 *如果是PK的unique index设置为unusable之后将不能向其中插入数据，这样在imp 时使用skip_unusable参数也不起作用，不管数据重复与否都无法
插入表。
*/

 

SQL> create table test(id int, name varchar2(32));

Table created.

SQL> alter table test add constraint pk_test_id primary key (id);

Table altered.

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

1 row created.

SQL> insert into test values(2, 'b');

1 row created.

SQL> commit;

Commit complete.

SQL> select index_name from user_indexes where table_name = 'TEST';

INDEX_NAME
------------------------------
PK_TEST_ID

SQL> alter index pk_test_id unusable;

Index altered.

SQL> select index_name, status from user_indexes where table_name = 'TEST';

INDEX_NAME                     STATUS
------------------------------ --------
PK_TEST_ID                     UNUSABLE

SQL> SELECT * from test;

        ID NAME
---------- --------------------------------
         1 a
         2 b
         3 c
         4 d
         5 e

SQL> insert into test values(5, 'e');
insert into test values(5, 'e')
*
ERROR at line 1:
ORA-01502: index 'ZHF.PK_TEST_ID' or partition of such index is in unusable
state

SQL>

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

转载于:http://blog.itpub.net/10173379/viewspace-625350/