| Partition error [message #411130] |
Thu, 02 July 2009 01:59  |
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 #411132 is a reply to message #411131] |
Thu, 02 July 2009 02:16  |
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 |
Frank
Messages: 7901
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 |
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
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
