DBMS_STATS Fails With ORA-1502 or ORA-20000; Subpartition Index Shows Usable (Doc ID 759011.1)

Last updated on FEBRUARY 09, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 9.0.1.0 and later
Information in this document applies to any platform.
***Checked for relevance on 11-Jan-2012***


Symptoms

When gathering statistics on a subpartition, dbms_stats.gather_table_stats fails with ORA-1502 or ORA-20000 "index or partition of such index is in unusable state."

Here is the dbms_stats syntax that was used:

exec dbms_stats.gather_table_stats(ownname => 'EDSTEST', -
                                   tabname => 'WDH_TRAN_FACT', -
                                   granularity => 'SUBPARTITION', -
                                   partname => 'C_200301_NV', -
                                   method_opt => 'for all indexed columns size 1', -
                                   estimate_percent => 5, -
                                   cascade => true ) ;
ERROR at line 1:
ORA-20000: index "EDSTEST"."WDH_TRAN_FACT_09"  or partition of such index is in
unusable state
ORA-06512: at "SYS.DBMS_STATS", line 13182
ORA-06512: at "SYS.DBMS_STATS", line 13202
ORA-06512: at line 1

The index looks like it is usable:

select subpartition_name idx_subpart, status from user_ind_subpartitions
       where index_name = 'WDH_TRAN_FACT_09'
         and partition_name = 'C_200301' ;


IDX_SUBPART                    STATUS
------------------------------ --------
C_200301_NV                    USABLE   <-this is the index we assume is used
C_200301_PA                    UNUSABLE
C_200301_XX                    USABLE

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms