Oracle Database Discussions

Hi,

We are on oracle 11.2.0.2 on Solaris 10. Today i got this ORA-01502 index is in unusable state error - which is something strange and I don't know why it would have happened...there was only a delete on the table that was happening and it caused that error. Any clue on what is the root cause will be helpful to me - I fixed it but I am afraid it may happen again if I don't address the root cause of that error.

 

--a simple harmless looking DML that caused the error: 
DELETE FROM SERVICE_RULE WHERE APLN_ID = 'HR'; 

--Below is the error message from front end 
[exec] ERROR at line 1: 
     [exec] ORA-01502: index 'SCHEMA1.PK_SERVICE_RULE' or partition of 
     [exec] such index is in unusable state 


--table structure 
CREATE TABLE SERVICE_RULE 
( 
  RULE_OID  NUMBER(3)                NOT NULL, 
  APLN_ID               VARCHAR2(50 BYTE)        NOT NULL, 
  EXCLUSIVE_EXECUTION  NUMBER(1) 
); 


CREATE UNIQUE INDEX PK_SERVICE_RULE ON SERVICE_RULE 
(SERVICE_UI_RULE_OID); 


CREATE INDEX SERVICE_RULE_IDX01 ON SERVICE_RULE 
(APLN_ID); 


ALTER TABLE SERVICE_RULE ADD ( 
  CONSTRAINT PK_SERVICE_RULE 
  PRIMARY KEY 
  (RULE_OID) 
  USING INDEX PK_SERVICE_RULE 
  ENABLE VALIDATE); 

Rebuilding the primary key fixed the issue but I am afraid if it may happen again in future. Can someone please suggest on what could have caused this error?
Thanks,
OrauserN