English

Error: ORA-31684 'Index already exists' when importing ST_GEOMETRY spatial indexes using Oracle data pump utility (IMPDP).

Error Message

While importing the ST_GEOMETRY spatial indexes using the Oracle data pump utility (IMPDP), the following error messages may be returned:

Processing object type TABLE_EXPORT/TABLE/INDEX/DOMAIN_INDEX/INDEX

"ORA-31684: Object type INDEX:"SDE"."A1_IX1" already exists"

"ORA-31684: Object type INDEX:"<OWNER>"."<SPATIAL_INDEX_NAME>" already exists"

"ORA-31684: Object type INDEX:"<OWNER>"."<SPATIAL_INDEX_NAME>" already exists"

Job "<IMP_USER>"."SYS_IMPORT_FULL_01" completed with <n> error(s) at <timestamp> elapsed 0 00:00:03

Cause

For an ST_GEOMETRY spatial index the parameters, such as Spatial Reference ID (SRID), numeric characters of spatial grids, including the decimal symbol, are commonly different on the target database compared to those from the source database. Hence, during the import, the domain-specific implementation must create the index with new parameters.

However, the import process of the Oracle data pump utility is not aware of the index created by the domain-specific implementation, and it continues its attempt to create the index using original parameters stored from the source database. This causes the generation of the ORA-31684 'Index already exists' error.

Solution or Workaround

This error message can be ignored, since the index with the correct parameters has already been created.

The validity of the imported spatial indexes can be checked by querying the target database, for example:

SQL> SELECT INDEX_NAME, STATUS FROM ALL_INDEXES WHERE INDEX_TYPE='DOMAIN';


Note:
A bug/enhancement has been submitted to Oracle for further review:

Bug 19862783 - IMPDP THROWS ORA-31684 FOR DOMAIN INDEX CREATED BY CUSTOM PLSQL