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

最新推荐文章于 2024-07-30 08:30:00 发布

原创最新推荐文章于 2024-07-30 08:30:00 发布 · 1w 阅读

3 ·

CC 4.0 BY-SA版权

文章标签：

#table #user #database #sql #oracle

本文讨论了在Oracle数据库中遇到的分区表索引失效问题，详细介绍了导致问题的原因，并提供了重建分区表索引的步骤。通过执行特定的SQL命令，可以成功修复索引并重新收集分区表统计信息。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

收集分区表统计信息的时候遇到了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