ORA-01502& ORA-14086: a partitioned index may not be rebuilt as a whole

收集分区表统计信息的时候遇到了ORA-01502分区表索引失效的错误。

 

重新REBUILD分区表的分区索引后能正常收集分区表统计信息。

 

rebuild分区表分区索引的方法：
ALTER INDEX &index_name  REBUILD PARTITION &PARTITION_NAME;

 

注：这里的PARTITION_NAME指USER_IND_PARTITIONS中的PARTITION_NAME(索引分区中的索引分区名);

 

导致分区表索引失效的原因可能是在操作分区表时，忘记使用了update indexes导致。

 

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0
Connected as TEST

SQL> ANALYZE TABLE T_PARTITION_HASH  COMPUTE STATISTICS;
ANALYZE TABLE T_PARTITION_HASH  COMPUTE STATISTICS
ORA-01502: index 'TEST.IDX_PART_HASH_ID' or partition of such index is in unusable state

 

SQL> EXEC dbms_stats.gather_table_stats(ownname =>'TEST' ,tabname =>'T_PARTITION_HASH' ,cascade => TRUE );
begin dbms_stats.gather_table_stats(ownname =>'TEST' ,tabname =>'T_PARTITION_HASH' ,cascade => TRUE ); end;
ORA-20000: index "TEST"."IDX_PART_HASH_ID"  or partition of such index is in unusable state
ORA-06512: at "SYS.DBMS_STATS", line 13437
ORA-06512: at "SYS.DBMS_STATS", line 13457
ORA-06512: at line 1

 

 

--查询分区表索引所在的分区
SQL> SELECT PI.TABLE_NAME,
  2         IP.INDEX_NAME,
  3         IP.PARTITION_NAME,
  4         IP.STATUS,
  5         IP.GLOBAL_STATS
  6    FROM USER_PART_INDEXES PI, USER_IND_PARTITIONS IP
  7   WHERE PI.INDEX_NAME = IP.INDEX_NAME
  8     AND PI.TABLE_NAME = 'T_PARTITION_HASH';

 

TABLE_NAME               INDEX_NAME                 PARTITION_NAME     STATUS          GLOBAL_STATS
-------------------------    --------------------------     ------------------------- --------------     ------------
T_PARTITION_HASH     IDX_PART_HASH_ID      SYS_P25                   UNUSABLE      NO
T_PARTITION_HASH     IDX_PART_HASH_ID      SYS_P26                   UNUSABLE      NO
T_PARTITION_HASH     IDX_PART_HASH_ID      SYS_P24                   UNUSABLE      NO
                     
SQL> ALTER INDEX IDX_PART_HASH_ID  REBUILD;
                     
ALTER INDEX IDX_PART_HASH_ID  REBUILD

ORA-14086: a partitioned index may not be rebuilt as a whole

SQL> ALTER INDEX IDX_PART_HASH_ID  REBUILD PARTITION SYS_P24;
Index altered

 

SQL> ALTER INDEX IDX_PART_HASH_ID  REBUILD PARTITION SYS_P25;
Index altered

 

SQL> ALTER INDEX IDX_PART_HASH_ID  REBUILD PARTITION SYS_P26;
Index altered

 

SQL> ANALYZE TABLE T_PARTITION_HASH  COMPUTE STATISTICS;
Table analyzed

SQL> EXEC dbms_stats.gather_table_stats(ownname =>'TEST' ,tabname =>'T_PARTITION_HASH' ,cascade => TRUE );

PL/SQL procedure successfully completed