Why Index in Unusable State?
Oracle Indexes Can Go into a Unusable State After Maintenance Operation on the Table or If the Index Is Marked as 'Unusable' with an Alter Index Command. a...
Oracle indexes can go into a UNUSABLE state after maintenance operation on the table or if the index is marked as 'unusable' with an ALTER INDEX command. A direct path load against a table or partition will also leave its indexes unusable.
Why does the unusable state have an index?
Indexes can become invalid or unusable whenever a DBA tasks shifts the ROWID values, thereby requiring an index rebuild. These DBA tasks that shift table ROWID's include: Table partition maintenance - Alter commands (move, split or truncate partition) will shift ROWID's, making the index invalid and unusable.
How do you fix unusable indexes?
...
This can be avoided by using the ONLINE keyword.
- Create Table and insert row in it: —————————————- ...
- Check the Index Status. ...
- Move the Table and Check Status: ...
- Rebuild The Index:
James Sterling reports on renewable energy developments, climate policy, ecological conservation, and green tech innovations around the globe.
