Index gets into invalid status
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: Index gets into invalid status

  1. #1
    Join Date
    Mar 2002
    Posts
    200

    Index gets into invalid status

    When I moved a table from one TS to another, the indexes became invalid. why?

  2. #2
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    Because rowid of the rows in the table changes and index becomes unusable not invalid.
    Last edited by adewri; 03-14-2003 at 12:33 AM.
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  3. #3
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Relative file number changes...so does Block number....when u move a segment from one tablespace to other.

    Due to which RowId changes & index will be holding OLD rowid so it goes to INVALID state.

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  4. #4
    Join Date
    Mar 2002
    Posts
    200
    thanks for the reply guys. is this issue solved in 9i?

  5. #5
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    U need to rebuild index in 9i as well...

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  6. #6
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    Originally posted by abhaysk
    Due to which RowId changes & index will be holding OLD rowid so it goes to INVALID state.
    Abhay, here i would like to comment that status values for indexes can be 'VALID' or 'UNUSABLE'. Other stored objects can be 'VALID' or 'INVALID.' Either all_indexes, DBA_INDEXES or USER_INDEXES should be querried for the status of indexes.

    HTH
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  7. #7
    Join Date
    Mar 2002
    Posts
    200
    Originally posted by abhaysk
    U need to rebuild index in 9i as well...

    Abhay.
    Wonder why didn't Oracle take care of such small things in 9i. All it was supposed to do is rebuild the index whenever such operations happened. Wonder if there is any specific reason why they didn't do it (or did they overlook such issues !)

  8. #8
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by adewri
    Abhay, here i would like to comment that status values for indexes can be 'VALID' or 'UNUSABLE'. Other stored objects can be 'VALID' or 'INVALID.' Either all_indexes, DBA_INDEXES or USER_INDEXES should be querried for the status of indexes.

    HTH
    Amar I didnt literally mean INVALID...it was jus to empasize theat indexes wud be unused if u moved the table.

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  9. #9
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    Originally posted by abhaysk
    Amar I didnt literally mean INVALID...
    Abhay.
    Aah you mean to say "I literally mean INVALID" or did i get it wrong
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  10. #10
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by quester
    Wonder why didn't Oracle take care of such small things in 9i. All it was supposed to do is rebuild the index whenever such operations happened. Wonder if there is any specific reason why they didn't do it (or did they overlook such issues !)
    Good Q...i blv they wudnt have overlooked..

    Probably oracle corp guys might have thought....i mean logically...
    that when user wants to move a table, then he may want to recreate the index in diff tablespace...in that case it wud be uswise to rebuild itself...

    i am waiting for Jomodic to come up with resaons...

    abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

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