DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS Generates Duplicate Index Names on Interim Table

(Doc ID 1922809.1)

Last updated on SEPTEMBER 04, 2014

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.3 and later
Information in this document applies to any platform.

Symptoms

Source table has multiple indexes with very long names like "index_with_very_long_nm", "index_with_very_long_nm1", "index_with_very_long_nm2" that reside in more than one schema.

When trying to reorg such table with online DBMS_REDEFINITION, duplicate names are getting generated for some of the indexes on the interim table.  Due to this, the desired number of indexes were not created.

For example:
SCOTT.INDEX_WITH_VERY_LONG_NM1 ->"SCOTT"."TMP$$_INDEX_WITH_VERY_LONG_2"
SCOTT.INDEX_WITH_VERY_LONG_NM2 ->"SCOTT"."TMP$$_INDEX_WITH_VERY_LONG_2"

For above two indexes (nm1, nm2), DBMS_REDEFINITION creates only one index with the name "tmp$$_index_with_very_long_2". 

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms