ORA-39083: Object type INDEX failed to create with error:

Problem:

Recently while trying to import one schema from production database to our test environment, I had this error while trying to create an Index.

ORA-39083: Object type INDEX failed to create with error:

The steps I followed to export/import the schema were:

 On Source System:

• Creating a directory object on source system, grant read and write to directory.
• Starting the export

 $ expdp system/manager schemas=DWHS  directory=dmpdir dumpfile=DWHS_schema.dmp logfile=expdp_DWHS.log

• Starting the import 

 $ nohup impdp system/manager schemas= DWHWHS  directory=dmpdir dumpfile=DWHS_schema.dmp logfile=impdp_DWHS.log remap_schema=DWHS: DWHWHS remap_tablespace=DMD:DMD &

•   Crosscheck the total number of objects in source and target system.

SQL> Select count(*) from dba_objects where owner='DWHS';   COUNT(*) ----------        841

SQL> Select count(*) from dba_objects where owner='DWHWHS';   COUNT(*) ----------        627

SQL> Select owner,object_type,count(1) from dba_objects where owner='DWHS' group by owner,object_type; OWNER                          OBJECT_TYPE           COUNT(1) ------------------------------ ------------------- ---------- DWHS                           TABLE                      400 DWHS                           VIEW                        48 DWHS                           PACKAGE                      5 DWHS                           SEQUENCE                     6 DWHS                           PROCEDURE                    8 DWHS                           TRIGGER                      1 DWHS                           INDEX                      342 DWHS                           SYNONYM                     12 DWHS                           FUNCTION                     5 DWHS                           PACKAGE BODY                 5 DWHS                           DATABASE LINK                9

SQL> Select owner,object_type,count(1) from dba_objects where owner='DWHWHS' group by owner,object_type; OWNER                          OBJECT_TYPE           COUNT(1) ------------------------------ ------------------- ---------- DWHWHS                         FUNCTION                     5 DWHWHS                         SEQUENCE                     6 DWHWHS                         PROCEDURE                    8 DWHWHS                         VIEW                        48 DWHWHS                         INDEX                      129 DWHWHS                         TRIGGER                      1 DWHWHS                         PACKAGE                      5 DWHWHS                         PACKAGE BODY                 5 DWHWHS                         DATABASE LINK                8 DWHWHS                         TABLE                      400 DWHWHS                         SYNONYM                     12

I can clearly see that a major number of indexes were not imported, than to dig further I looked in the log file generated by the import command.And there you go, two many errors saying index failed to create.

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
ORA-39083: Object type INDEX failed to create with error:

Cause:

The reason these indexes are not being created is because the base object - table - exists.

Workaround:

• I would recommend to import the dump file by excluding the indexes and later you can import the indexes from the dump file.
• Even you can set table_exists_action clause to skip, truncate, or append, none of the dependent objects are created if the base objects already exists and is included in the data pump job. This includes indexes as well 
• In my case, I used append options which actually imports the data, but not the indexes

$ impdp system/manager directory=dmpdir dumpfile=DWHS_schema.dmp TABLE_EXISTS_ACTION=APPEND INCLUDE=INDEX logfile=impdp_DWHS1.log remap_schema=DWHS:DWHWHS

• When you say include=index, that is all that is included. Nothing else. No tables, no data, nothing. This is why you don't see any data being loaded. In fact, that table_exists_action=append doesn't do anything since data is not being loaded.
• If that doesn't work you can also use exclude, instead of using include. You know that you already have the tables, so if you just exclude the tables, you will get the data and the indexes.

$ impdp system/manager directory=dmpdir dumpfile=DWHS_schema.dmp logfile=impdp_DWHS1.log EXCLUDE=TABLE/TABLE remap_schema=DWHS:DWHWHS

• You don't want to just say exclude=table because this will exclude all objets with TABLE in them.

HTH

Thanks for reading.