Oracle Database Discussions

Hi

I am trying to find out "How to Disable a Index". I see syntax for alter index and there is one clause "Alter index index_name disable" but this doesn;t work.. I get error.. I tried all combinations, nothing work

SQL> alter index EMP_NO_UNQ_IND1 disable;
alter index EMP_NO_UNQ_IND1 disable
*
ERROR at line 1:
ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option



Here is what I am trying to do.. We are loading data using Informatica. So For every run, we want to disable index and after load, enable it back again.

When I use Unusable, it works, but I am not able to insert any data after I make index unusable.

SQL> alter index EMP_NO_UNQ_IND1 unusable;

Index altered.

SQL> insert into emp values (5,'XYZ','ZZZ');
insert into emp values (5,'XYZ','ZZZ')
*
ERROR at line 1:
ORA-01502: index 'C608942.EMP_NO_UNQ_IND1' or partition of such index is in
unusable state


SQL>


How do I get around this issue...

It can't be that hard.. I am missing some little string here.

Please advice

PG