SQL> 
SQL> /*
DOC>skip_unusable_indexes refres to DML, not select.
DOC>When queries specify a where clause on a column with an unusable index,
DOC>regardless of the skip_unusable_indexes setting, error ocurrs.
DOC>
DOC>skip_unusable_indexes=true will cause maintainence on
DOC>non-unique indexes to be avoided.
DOC>*/
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('c',3);

1 row created.

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

1 row created.

SQL> 
SQL> create unique index test3_a_uidx on test3_a(c1);

Index created.

SQL> create index test3_a_idx on test3_a(c2);

Index created.

SQL> 
SQL> alter index test3_a_uidx unusable;

Index altered.

SQL> alter index test3_a_idx unusable;

Index altered.

SQL> 
SQL> alter session set skip_unusable_indexes=true;

Session altered.

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


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_UIDX' or partition of such index is in unusable 
state 


SQL> alter index test3_a_uidx rebuild;

Index altered.

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


SQL> select * from test3_a where c1='a';

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

SQL> 
SQL> drop index test3_a_uidx;

Index dropped.

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

1 row created.

SQL> 
SQL> alter session set skip_unusable_indexes=false;

Session altered.

SQL> drop table test3_a;

Table dropped.

SQL> 
SQL> spool off