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