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.
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?
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.
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
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.