SQL> 
SQL> /*
DOC>The identical results as unique constraint.
DOC>*/
SQL> 
SQL> drop table test3_a;
drop table test3_a
           *
ERROR at line 1:
ORA-00942: table or view does not exist 


SQL> create table test3_a as select * from test3 where 1=2;

Table created.

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

1 row created.

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

1 row created.

SQL> insert into test3_a values('c',3);

1 row created.

SQL> insert into test3_a values('e',4);

1 row created.

SQL> alter table test3_a add constraint test3_a_pk primary key (c1);

Table altered.

SQL> 
SQL> alter table test3_a modify constraint test3_a_pk enable novalidate;

Table altered.

SQL> select STATUS,VALIDATED,INVALID from user_constraints where CONSTRAINT_NAME='TEST3_A_PK';

STATUS   VALIDATED     INVALID                                                  
-------- ------------- -------                                                  
ENABLED  NOT VALIDATED                                                          

SQL> insert into test3_a values('a',5);
insert into test3_a values('a',5)
*
ERROR at line 1:
ORA-00001: unique constraint (JYANG.TEST3_A_PK) violated 


SQL> 
SQL> -- index gone
SQL> alter table test3_a modify constraint test3_a_pk disable validate;

Table altered.

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

no rows selected

SQL> insert into test3_a values('a',5);
insert into test3_a values('a',5)
*
ERROR at line 1:
ORA-25128: No insert/update/delete on table with constraint (JYANG.TEST3_A_PK) 
disabled and validated 


SQL> 
SQL> drop table test3_a;

Table dropped.

SQL> create table test3_a as select * from test3 where 1=2;

Table created.

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

1 row created.

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

1 row created.

SQL> insert into test3_a values('a',3);

1 row created.

SQL> -- Important: ORACLE tried to create a "unique" index here and failed.
SQL> -- Had a non-unique index present for the PK to utilized, the statement would have succeeded.
SQL> alter table test3_a add constraint test3_a_pk primary key (c1) enable novalidate;
alter table test3_a add constraint test3_a_pk primary key (c1) enable novalidate
                                   *
ERROR at line 1:
ORA-02437: cannot validate (JYANG.TEST3_A_PK) - primary key violated 


SQL> 
SQL> delete from test3_a where c2=3;

1 row deleted.

SQL> alter table test3_a add constraint test3_a_pk primary key (c1);

Table altered.

SQL> 
SQL> -- What happens when the PK index is unusable?
SQL> alter index test3_a_pk unusable;

Index altered.

SQL> select STATUS,VALIDATED,INVALID from user_constraints where CONSTRAINT_NAME='TEST3_A_PK';

STATUS   VALIDATED     INVALID                                                  
-------- ------------- -------                                                  
ENABLED  VALIDATED                                                              

SQL> select * from test3_a;

C1         C2                                                                   
-- ----------                                                                   
a           1                                                                   
b           2                                                                   

SQL> select * from test3_a where c2=1;

C1         C2                                                                   
-- ----------                                                                   
a           1                                                                   

SQL> select * from test3_a where c1='a';
select * from test3_a where c1='a'
*
ERROR at line 1:
ORA-01502: index 'JYANG.TEST3_A_PK' or partition of such index is in unusable 
state 


SQL> insert into test3_a values('c',3);
insert into test3_a values('c',3)
*
ERROR at line 1:
ORA-01502: index 'JYANG.TEST3_A_PK' or partition of such index is in unusable 
state 


SQL> 
SQL> -- skip_unusable_indexes useless against unique indexes
SQL> alter session set skip_unusable_indexes=true;

Session altered.

SQL> select * from test3_a where c1='a';
select * from test3_a where c1='a'
*
ERROR at line 1:
ORA-01502: index 'JYANG.TEST3_A_PK' or partition of such index is in unusable 
state 


SQL> insert into test3_a values('c',3);
insert into test3_a values('c',3)
*
ERROR at line 1:
ORA-01502: index 'JYANG.TEST3_A_PK' or partition of such index is in unusable 
state 


SQL> 
SQL> alter session set skip_unusable_indexes=false;

Session altered.

SQL> drop table test3_a;

Table dropped.

SQL> 
SQL> spool off