ORA-20000: index “OC_PYM”.”IX_PYM_INV_INVOICE_01″ or partition of such index is in unusable state


When I have starting today EXEC DBMS_STATS.gather_index_stats I see problem in below
SQL> EXEC DBMS_STATS.gather_index_stats('OC_PYM', 'IX_PYM_INV_INVOICE_01');
BEGIN DBMS_STATS.gather_index_stats('OC_PYM', 'IX_PYM_INV_INVOICE_01'); END;
*
ERROR at line 1
ORA-20000: index "OC_PYM"."IX_PYM_INV_INVOICE_01" or partition of such index
is in unusable state
ORA-06512: at "SYS.DBMS_STATS", line 18361
ORA-06512: at "SYS.DBMS_STATS", line 18402
ORA-06512: at line 1
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;
Or Partition
ALTER INDEX <index_name> REBUILD PARTITION <partition_name>;

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

%d bloggers like this: