Today has appeared the error below in the alert.log of a database;
Errors in file $1$DGA2822:[TBOX.][USER]SCISCO_CORN_FG_ORACLE_354.TRC;:
ORA-20000: index “ELLIS”.”CATCHUP_STOCKS_I” or partition of such index is in unusable state
The error has occurred when someone was trying to access a partition of the index, in this case it was in unusable state because a direct load failed. To solve the problem we must rebuild the index that is unusable, if they are several unusables all them and if it is the entire index, the all index. With the following command our problem is solved.
ALTER INDEX <index_name> REBUILD PARTITION <partition_name>;
- It is good to have a check to verify the status of the indexes or invalid objects periodically.
- In this case the load is part of a chain (No database scheduler is used) would have to put more control logic after the load to verify that everything is ok.
HTH – Antonio NAVARRO