RESEND: ORA-01502: index 'SHOPPINGSA.SCAN_YDCMFFLT_IDX2' or partition of such index is in unusable state

Sorry, for the fat finger on first message !!!

Group,
RDBMS Version: 9.2.0.5
Operating System and Version: HP-UX B.11.11 U 9000/800 Error Number (if applicable): ORA-01502
Product (i.e. SQL*Loader, Import, etc.): SQLPLUS

Applied release 2 patch set 4 version 9.2.0.5.0 to 9.2.0.3 EE

After patching I also applied: IAVA's 3811906 and 3835964.

I am now getting ORA-01502.
If I run:
ALTER TABLE scan_ydcmfflt
MODIFY PARTITION cmf_1 REBUILD UNUSABLE LOCAL INDEXES; the problem goes away.
Did not have this error until patch set install !!

 1 select index_name, status, last_analyzed  2 from dba_ind_partitions
 3* where index_owner = 'SHOPPINGSA'
DIN:shoppingsa> /

NDEX_NAME                     STATUS   LAST_ANAL
----------------------------- -------- ---------
CAN_CONTRACT_IDX1             UNUSABLE 04-JAN-05
CAN_CONTRACT_IDX1             UNUSABLE 04-JAN-05
CAN_CONTRACT_IDX1             UNUSABLE 04-JAN-05
CAN_CONTRACT_IDX1             UNUSABLE 04-JAN-05
CAN_CONTRACT_IDX1             UNUSABLE 04-JAN-05
CAN_CONTRACT_IDX1             UNUSABLE 04-JAN-05
CAN_CONTRACT_IDX1             UNUSABLE 04-JAN-05
CAN_CONTRACT_IDX1             UNUSABLE 04-JAN-05
CAN_CONTRACT_ITEM_UPC_IDX     UNUSABLE 04-JAN-05

AFTER INDEX REBUILD:
ODIN:shoppingsa> select index_name, status, last_analyzed   2 from dba_ind_partitions
  3 where index_owner = 'SHOPPINGSA';

INDEX_NAME                     STATUS   LAST_ANAL
------------------------------ -------- ---------
SCAN_CONTRACT_IDX1             USABLE
SCAN_CONTRACT_IDX1             USABLE
SCAN_CONTRACT_IDX1             USABLE
SCAN_CONTRACT_IDX1             USABLE
SCAN_CONTRACT_IDX1             USABLE
SCAN_CONTRACT_IDX1             USABLE
SCAN_CONTRACT_IDX1             USABLE
SCAN_CONTRACT_IDX1             USABLE
SCAN_CONTRACT_ITEM_UPC_IDX     USABLE

TIA Al Rusnak
Oracle Certified Professional
DBA - CISIS, Computer Operations

• 804-734-8210
• george.rusnak_at_deca.mil

--
http://www.freelists.org/webpage/oracle-l