-
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
-
I think if you use SQL*Loader to import data with direct=y, it makes the index unusuable. Confirm this.
-
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
-
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.
-
Actually, only one index becomes unusable. The datafiles are fine. I just want to know what are scenarios for index corruptions. Thanks, Halo.
Dragon
-
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]
-
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
-
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.
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|