| ORA-01502: index or partition of such index is in unusable state [message #386710] |
Mon, 16 February 2009 18:04  |
Ricky_1362002
Messages: 111
Registered: February 2009
|
Senior Member |
|
|
ORA-01502:index 'A' or partition of such index is in unusable state
But the index is working fine right now.
The error is occurring only at some times for a cron job, which is scheduled to run every 5 minutes.
The cron job actually uses sqlldr direct=true option, what does it do?
Does it drop the index and try to rebuild it during loading, why does this error usually comes into picture, i could not find the solution for this issue since a long time.
Please help me.
let me know if you need any more information.
|
|
|
|
|
|
|
|
|
|
| Re: ORA-01502: index or partition of such index is in unusable state [message #386714 is a reply to message #386710] |
Mon, 16 February 2009 18:26  |
Ricky_1362002
Messages: 111
Registered: February 2009
|
Senior Member |
|
|
Yes i did read it:
Indexes Left in an Unusable State
SQL*Loader leaves indexes in an Index Unusable state when the data segment being loaded becomes more up-to-date than the index segments that index it.
Any SQL statement that tries to use an index that is in an Index Unusable state returns an error. The following conditions cause a direct path load to leave an index or a partition of a partitioned index in an Index Unusable state:
* SQL*Loader runs out of space for the index and cannot update the index.
* The data is not in the order specified by the SORTED INDEXES clause.
* There is an instance failure, or the Oracle shadow process fails while building the index.
* There are duplicate keys in a unique index.
* Data savepoints are being used, and the load fails or is terminated by a keyboard interrupt after a data savepoint occurred.
But did not find the answer to "Does it again put the Index to a NON unusable (i mean normal) state"?
So, how to avoid this error in the future?
sorry to trouble you Mahesh, i am pretty new to Oracle.
Thank you very much again
-munna
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
