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 184.108.40.206 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:
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
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:
where index_name = 'WDH_TRAN_FACT_09'
and partition_name = 'C_200301' ;
C_200301_NV USABLE <-this is the index we assume is used
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.|