Home » RDBMS Server » Server Utilities » ORA-01502: index or partition of such index is in unusable state (Oracle 9i)
ORA-01502: index or partition of such index is in unusable state [message #386710] Mon, 16 February 2009 18:04 Go to next message
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 #386711 is a reply to message #386710] Mon, 16 February 2009 18:10 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96652/ch09.htm#1007759
Re: ORA-01502: index or partition of such index is in unusable state [message #386712 is a reply to message #386710] Mon, 16 February 2009 18:15 Go to previous messageGo to next message
Ricky_1362002
Messages: 111
Registered: February 2009
Senior Member
Thank you very much Mr.Mahesh for your quick reply.

But as of now, the index is NOT in unusable state and also its status is VALID.

But my question is, after the SQL Loader loading is done, will it put the index back in USABLE state, because this error occurs only some times of that cron job, i dont know why.



-thanks again
munna
Re: ORA-01502: index or partition of such index is in unusable state [message #386713 is a reply to message #386712] Mon, 16 February 2009 18:19 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> because this error occurs only some times of that cron job, i dont know why.

The document exactly talks about that.
Did you read it?
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 Go to previous messageGo to next message
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
Re: ORA-01502: index or partition of such index is in unusable state [message #386716 is a reply to message #386714] Mon, 16 February 2009 18:35 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>But did not find the answer to "Does it again put the Index to a NON unusable (i mean normal) state"?

I am just rephrasing what is been already said in doc.
Index will be in normal state. If Oracle is not happy with it (for the specific reasons mentioned),
the index will be left in unusable state.

>>So, how to avoid this error in the future?
This error may occur under these conditions. Try to avoid them.

Quote:
* 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.

Re: ORA-01502: index or partition of such index is in unusable state [message #386719 is a reply to message #386710] Mon, 16 February 2009 18:53 Go to previous message
Ricky_1362002
Messages: 111
Registered: February 2009
Senior Member
Thank you very much Mahesh.


-munna
Previous Topic: DB trigger (merged)
Next Topic: SQL LOADER
Goto Forum:
  


Current Time: Mon Dec 05 02:49:13 CST 2016

Total time taken to generate the page: 0.07914 seconds