oracle ora 14086,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