ORA-01502(such index is unusable state)
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: ORA-01502(such index is unusable state)

  1. #1
    Join Date
    May 2002
    Posts
    232
    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
    kavitha

  2. #2
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    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

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

  4. #4
    Join Date
    Feb 2001
    Posts
    180
    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

    Regards
    Ben de Boer

  5. #5
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    Are you either,

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

    OCP 8i, 9i DBA
    Brisbane Australia

  6. #6
    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?
    www.cnoug.org

  7. #7
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203

    [/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!
    OCP 8i, 9i DBA
    Brisbane Australia

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width