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

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

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