ORA-01502(such index is unusable state)

[kavithared]

Hiii,i get frequently above error,
Every time i am rebuild the index,then
i was solve the problem.
Why this error occur?
Is there any permenent solution to overcome this problem.
thanks

[SANJAY_G]

ORA-01502 index 'string.string' or partition of such index is in unusable state

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.



Sanjay

[chao_ping]

So find out which action caused the index be in invalid mode?
when was the last ddl, get the root cause and solve it.

[bensr]

I'm sorry I can't recall it completely,
but we had the same problems too.
It was cased by a pretty complicated DML-query,
including user-function , inner selects, etc.

Every time a batch-procedure ran, it had the same problems.
I rewrote the query avoiding complex retrieve queries.

Try to locate the problem statement in it.

Good luck

[grjohnson]

Are you either,

a). Doing partition manipulation?
b). Perfoming Direct Imports?

[chao_ping]

Originally posted by grjohnson
Are you either,

a). Doing partition manipulation?
b). Perfoming Direct Imports?

hei,
there is no concept like direct import, just direct load?

[grjohnson]

[/B][/QUOTE]
hei,
there is no concept like direct import, just direct load? [/B][/QUOTE]

Yes, yes, yes... nice pick up.

Sorry, typo, Direct SQLLDR loads, is what I should've say.. Cheezzzz!