RESEND: ORA-01502: index 'SHOPPINGSA.SCAN_YDCMFFLT_IDX2' or partition of such index is in unusable state
- From: "Rusnak, George A. (SEC-Lee) CTR" <george.rusnak@xxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 8 Mar 2005 13:17:14 -0500
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@xxxxxxxx
--
//www.freelists.org/webpage/oracle-l
Other related posts:
- » RESEND: ORA-01502: index 'SHOPPINGSA.SCAN_YDCMFFLT_IDX2' or partition of such index is in unusable state
