ALTER TABLE MOVE PARTITION failes with DBMS_INDEX_UTL ORA-01422

I have been contacted by one of our customers with quite interesting problem.

They have upgraded their oracle database to 11.2.0.3.1 and faced really non-typical problem:

ALTER TABLE OWN.TABLE_LOG MOVE PARTITION p_201202 tablespace LOG_ARCHIVE compress
UPDATE INDEXES (
   OWN.I_TABLE_LOG_SYSTEMDAY (partition p_201202 tablespace LOG_ARCHIVE)
 , OWN.I_TABLE_LOG_ID (partition p_201202 tablespace LOG_ARCHIVE)
 )
 ;

DBMS_INDEX_UTL: Error executing ALTER INDEX "OWN"."I_TABLE_LOG_SYSTEMDAY" REBUILD
 PARTITION "p_201202" NOPARALLEL
 DBMS_INDEX_UTL: Caused by ORA-01422: exact fetch returns more than requested number of rows
 DBMS_INDEX_UTL: Error executing ALTER INDEX "OWN"."I_TABLE_LOG_ID"
 REBUILD  PARTITION "p_201202" NOPARALLEL
 DBMS_INDEX_UTL: Caused by ORA-01422: exact fetch returns more than requested number of rows

Customer’s DBA was able to rebuild mentioned indexes by running ALTER INDEX command provided inside error message, so why they don’t work inside ALTER TABLE … MOVE PARTITION … UPDATE INDEXES ?

There are not many references to DBMS_INDEX_UTL on My Oracle Support site and this one is definitely not among them, so I have decided that it may be a new BUG or some kind of Dictionary Corruption

Continue reading