OraFAQ Forum: SQL & PL/SQL » ORA-01502: index or partition of such index is in unusable state

Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01502: index or partition of such index is in unusable state (Oracle 9i , Windows)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-01502: index or partition of such index is in unusable state [message #484315]

Mon, 29 November 2010 10:46

palanisvr
Messages: 21
Registered: March 2007

Junior Member

Hi Gurus,

I am facing the error "ORA-01502: index or partition of such index is in unusable state " while loading the text data using
sql loader with direct path (direct = Y ,rows = 10000) option. Table consists an composite non unique index. If I query the dba indexes for the effected index it shows the index status as VALID. There was no maintaince done on the effected table or index. I have tried loading the same data using conventional path but didn't found any issues for the same.

I appreciate your help on this to solve this issue.

With Regards,
Palani

Report message to a moderator

Re: ORA-01502: index or partition of such index is in unusable state [message #484317 is a reply to message #484315]

Mon, 29 November 2010 10:48

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

01502, 00000, "index '%s.%s' or partition of such index is in unusable state"
// MERGE: 1489 RENUMBERED TO 1502
// *Cause: 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
// *Action: DROP the specified index, or REBUILD the specified index, or
//         REBUILD the unusable index partition

Report message to a moderator

Re: ORA-01502: index or partition of such index is in unusable state [message #484319 is a reply to message #484317]

Mon, 29 November 2010 10:58

palanisvr
Messages: 21
Registered: March 2007

Junior Member

BLackSwan,

Good day.
by any chance could we identify the reason for the failure if so can you suggest to avoid the same in the future.

Cheers,
Palani

Report message to a moderator

Re: ORA-01502: index or partition of such index is in unusable state [message #484320 is a reply to message #484319]

Mon, 29 November 2010 11:02

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>by any chance could we identify the reason for the failure if so can you suggest to avoid the same in the future.
DIRECT=Y (direct path load) invalidates partition index.
Either rebuild index after load or do not use DIRECT=Y; it is your choice.

Report message to a moderator

Previous Topic:	Query to find values are not present in table.
Next Topic:	Database design help

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Apr 13 09:54:42 CDT 2026