ORA-20000: index or partition of such index is in unusable state

ORA-20000 index is unusable

The Problem

During one of our overnight jobs which gathers statistics on several tables after having data loaded into them, the job failed with the ORA-20000 error message. The problem was because we had a partition splitting job which ran against the table which caused the indexes to become unusable after the partition had been split. The indexes were at the global level on the table which means that any DDL operation on the table can make them unusable. The full error message we were receiving was:

ORA-20000: index “<index_name>” or partition of such index is in unusable state

The Solution

You need to re-build the index or the partition of the index which is in an unusable state. The commands below will achieve this.


ALTER INDEX <index_name> REBUILD PARTITION <partition_name>;

You can then go ahead and re-run the stats gathering operation. Be aware that the rebuilding of this index may put additional load on your system, so think about when you do it. Also, if the index is invalid then you may have queries which are not running properly against this table if they would normally use the index. This may cause the query to full scan the table and put additional load on your database server so check that this is not already happening.

The queries you can use to find out the INVALID and UNUSABLE indexes are as follows:

SELECT index_name, table_name, status
FROM dba_indexes
WHERE status != 'VALID'
AND owner = 'owner';

SELECT index_name,partition_name,status
FROM dba_ind_partitions
WHERE status != 'USABLE'
AND index_owner = 'owner';

If you are running some kind of partition maintenance operation and don’t want your indexes to become unusable there is a way to get around it. You can use the UPDATE GLOBAL INDEXES keyword (I think it was valid as of Oracle 10g) to make sure that any partition maintenance operations will also update the index as it goes along. This means that you can have 100% availability while also carrying out the partition maintenance. Obviously there is a downside…And that is increased resource usage and extended running time for your operation. But, you have a choice to have downtime and run the operation quickly or no downtime and the operation take longer and use more resource. I have written another article about this called Oracle Partitioning Fundamentals

Hopefully that has helped you to resolve the ORA-20000 error message and given you some ideas about how to prevent this from happening and also to ensure you have higher levels of availability when using partitioning in Oracle database.

Like it, share it...

Category: Indexes

Related Posts

Comments (2)

Trackback URL | Comments RSS Feed

  1. shafmoh says:

    Nicely explained.

Leave a Reply

Your email address will not be published. Required fields are marked *