ORA-20000 Error

Today has appeared the error below in the alert.log of a database;

 

1-SEP-2014 04:30:14.61:
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>;

 

Conclusions;

  • 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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s