index corruption
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 20

Thread: index corruption

Hybrid View

  1. #1
    Join Date
    Feb 2000
    Location
    New York,U.S.A.
    Posts
    245
    Hi, all
    Recently, I found that one index becomes unusable, and I did not see any error message which caused the index to be unusable. Could any one give me some scenarios which can cause an index to be unusable. Thanks.

    Dragon

  2. #2
    Join Date
    Apr 2000
    Location
    Edison, NJ
    Posts
    759
    I think if you use SQL*Loader to import data with direct=y, it makes the index unusuable. Confirm this.

  3. #3
    Join Date
    Feb 2000
    Location
    New York,U.S.A.
    Posts
    245
    Thanks, Halo,
    I did not use sqlloader recently. Could it be disk corruption? Because my disks are mirrored and self corrective, but the indexes happened to be in that disk got corrupted and need rebuild? Does that sound logical?

    Dragon

  4. #4
    Join Date
    Apr 2000
    Location
    Edison, NJ
    Posts
    759
    Are all the indexes corrupted?? Could be something serious. Check the dbf belonging to the ts using dbv, and tell me if there's any media/block corruption. Also check alert log for any errors, and any trace files if any.

  5. #5
    Join Date
    Feb 2000
    Location
    New York,U.S.A.
    Posts
    245
    Actually, only one index becomes unusable. The datafiles are fine. I just want to know what are scenarios for index corruptions. Thanks, Halo.

    Dragon

  6. #6
    Join Date
    Apr 2000
    Location
    Edison, NJ
    Posts
    759
    Atleast there's no media corruption, good. Analyze the table and index(es) and check if it's still in the unusuable state. By the way, did you use the: alter table move command?? This can also mark them unusuable.
    Check DocID 176405.999 on Metalink

    [Edited by Halo on 05-04-2001 at 03:20 PM]

  7. #7
    Join Date
    Oct 2000
    Location
    Dallas:TX:USA
    Posts
    407
    you can try REBUILDing the index (if you have enough space in the datafiles). This will recreate the index and may show you errors if the disk is corrupt.

    Halo,
    I didn't know the indexes would be unusable by moving the tables. whay would that happen ?

    - Rajeev
    Rajeev Suri

  8. #8
    Join Date
    Apr 2000
    Location
    Edison, NJ
    Posts
    759
    I didn't know about it either! I think it's because since the table is sorta 'recreated' again, in invalidates the indexes, and would have to be rebuilt. Found out from the Doc. that I mentioned earlier.

  9. #9
    Join Date
    Nov 2000
    Posts
    344
    An 'Alter table move' internally does a 'create as select' (which is why you can't do an alter table move if your table contains LONG columns). So the rowids for all of the rows change. The index becomes unusable because the rowid on the indexes no longer point to the rows of the table.

    -John

  10. #10
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    I thought I had heard that they were automatically re-built now after a move? Or am I just taking too much medication?

    - Chris

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