My Oracle Support Banner

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 and later
Information in this document applies to any platform.
***Checked for relevance on 11-Jan-2012***


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


To view full details, sign in with your My Oracle Support account.

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

In this Document

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.
My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.