Home » SQL & PL/SQL » SQL & PL/SQL » Partition error (Oracle 9i/unix)
Partition error [message #411130] Thu, 02 July 2009 01:59 Go to next message
findnags
Messages: 3
Registered: July 2009
Location: Bengalooru
Junior Member
Hi

I am getting the error: ORA-20101: ORA-01502: index 'DWCC.PRD_SLS_IND' or partition of such index is in unusabl
ORA-06512: at "MISC.GS_EXC", line 323
ORA-06512: at "DWCC.DO_PARTITION", line 374
ORA-06512: at "DWCC.DO_PARTITION", line 837
ORA-06512: at line 2

When I try to do partition.

Can anyone help me in this?

Regards
Nags
Re: Partition error [message #411131 is a reply to message #411130] Thu, 02 July 2009 02:01 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
... and which part of all the answers you found through Google did you not understand?
Re: Partition error [message #411132 is a reply to message #411131] Thu, 02 July 2009 02:16 Go to previous messageGo to next message
findnags
Messages: 3
Registered: July 2009
Location: Bengalooru
Junior Member
Actually when i googled, I got the following:

Reasons for the error:
* 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

==

I am not using SQL*Loader. SORTED INDEXES clause is not used. No instance failure, or any other failures. No duplicate keys or any other such reasons.
Re: Partition error [message #411133 is a reply to message #411132] Thu, 02 July 2009 02:20 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
It could also be a result of some other Direct Load or DDL action. Source
Quote:
The error indicates an attempt has been made to access an index or index partition that has been marked unusable by a direct load or by a DDL operation.

The problem usually happens when using the Direct Path for the SQL*Loader, Direct Load or DDL operations. This requires enough temporary space to build all indexes of the table. If there is no enough space in TEMP tablespace, all rows will still be loaded and imported, but the indices are left with STATUS = 'INVALID'.

Invalid indexes can be checked with a SELECT * from USER_INDEXES WHERE STATUS = 'INVALID'; SQL statement.

Solution to this error is simple. You can:

1. Drop the specified index and/or recreate the index
2. Rebuild the specified index
3. Rebuild the unusable index partition

Generally, the following SQL manipulation language will be able to rebuild the unusable index:

ALTER INDEX index_name REBUILD


Does this help you?
Re: Partition error [message #411136 is a reply to message #411133] Thu, 02 July 2009 02:26 Go to previous messageGo to next message
findnags
Messages: 3
Registered: July 2009
Location: Bengalooru
Junior Member
Thanks for the response.

Rebuilding makes it work. But when I run the partition again, I get same error. Actually we have a package DO_PARTITION which does exchange partition. End users are going to run this package to do exchange partition. I cannot tell them to rebuild the index whenever this error occurs. Is there anyway which could be handled programmatically?

Thanks
Re: Partition error [message #411137 is a reply to message #411136] Thu, 02 July 2009 02:28 Go to previous message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Include it in your procedure.

Regards
Michel
Previous Topic: Tracing Updates
Next Topic: Deletion from tables as new tables are added to schema
Goto Forum:
  


Current Time: Thu Dec 08 02:29:17 CST 2016

Total time taken to generate the page: 0.13075 seconds