ORA-39083: Object type failed to create with error

I got ” ORA-39083: Object type failed to create with error ”  error in Oracle database.

ORA-39083: Object type failed to create with error

Details of error are as follows.

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
ORA-39083: Object type TABLE_STATISTICS failed to create with error:

A DataPump import using the parameter REMAP_SCHEMA reports errors ORA-39083 and ORA-1403 while importing index statistics:

...
ORA-39083: Object type INDEX_STATISTICS failed to create with error:
ORA-01403: no data found
ORA-01403: no data found
Failing sql is:
DECLARE IND_NAME VARCHAR2(60); IND_OWNER VARCHAR2(60); BEGIN DELETE FROM
"SYS"."IMPDP_STATS"; SELECT index_name, index_owner INTO IND_NAME, IND_OWNER
FROM (SELECT UNIQUE sgc1.index_name, sgc1.index_owner,
COUNT(*) mycount
FROM sys.ku$_find_sgc_view sgc1,
TABLE (sgc1.col_list) myc

Object type failed to create with error

Here are the steps to reproduce the issue:

1. Create a table under “TEST0” schema:

#> sqlplus test0/password

DROP TABLE TEST0 PURGE;

CREATE TABLE TEST0
(
   COL1 NUMBER NOT NULL,
   COL2 NUMBER NOT NULL,
   COL3 NUMBER NOT NULL
)
TABLESPACE USERS;

ALTER TABLE TEST0 ADD (PRIMARY KEY (COL2, COL1, COL3) USING INDEX TABLESPACE USERS);

CREATE INDEX TEST0INDEX ON TEST0 (COL3, COL1, COL2) TABLESPACE USERS;

select object_name, object_type from user_objects;

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -------------------
TEST0                          TABLE
SYS_C009845                    INDEX <-- Index associated with PK constraint.
TEST0INDEX                     INDEX

2. Collect statistics for "TEST0" schema.

#> sqlplus system/password
exec dbms_stats.gather_schema_stats('TEST0',cascade => TRUE);

3. Execute the DataPump export an import process to reproduce the problem.

#> expdp system/password schemas=test0 dumpfile=test0.dmp
...

(completed successfully without warnings)

#> impdp system/password remap_schema=test0:test1 dumpfile=test0.dmp

...
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
ORA-39083: Object type INDEX_STATISTICS failed to create with error:
ORA-01403: no data found
ORA-01403: no data found
Failing sql is:
DECLARE IND_NAME VARCHAR2(60); IND_OWNER VARCHAR2(60); BEGIN DELETE FROM "SYS"."IMPDP_STATS";
SELECT index_name, index_owner INTO IND_NAME, IND_OWNER
FROM (SELECT UNIQUE sgc1.index_name, sgc1.index_owner,
COUNT(*) mycount
FROM sys.ku$_find_sgc_view sgc1,
TABLE (sgc1.col_list) myc
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA

4. Open a sqlplus session to check objects created for the import process under "TEST1" schema.

#> sqlplus test1/password

col object_name for a30
SELECT OBJECT_NAME, OBJECT_TYPE FROM USER_OBJECTS;

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -------------------
TEST0                          TABLE
TEST0INDEX                     INDEX

5. The index with the system generated name is missing because the primary key constraint was created in the source database without providing a name and before the named index was created, so an index with a system generated name was created to facilitate this primary key constraint. Upon import, Data Pump first imports the indexes (i.e. only the named index TEST0INDEX), and then imports the constraints.

When creating the primary key constraint, we can use the existing named index, so there is no need for the system generated index anymore, and hence it won’t be created. When import tries to import the statistics for the system generated index later on, it fails because the system generated index is missing.

1. Ignore the ORA-39083 (Object type INDEX_STATISTICS failed to create with error) and ORA-01403 (no data found) on this system generated index.

— or —

2. Ensure you create named indexes for PK constraints in the source database. E.g.:

-- instead of:
ALTER TABLE TEST0 ADD (PRIMARY KEY (COL2, COL1, COL3) USING INDEX TABLESPACE USERS);

-- do:
ALTER TABLE TEST0 ADD CONSTRAINT MY_PK PRIMARY KEY (COL2, COL1, COL3) USING INDEX TABLESPACE USERS);

— or —

3. Do a DataPump import excluding indexes, then import indexes. Doing that we guarantee that the index associated to the primary key constraints will be created first.

$ impdp system/password remap_schema=test0:test1 exclude=index dumpfile=test0.dmp

$ impdp system/password remap_schema=test0:test1 include=index dumpfile=test0.dmp

The following query can be executed to check the objects created after the import process is completed.

#> sqlplus test1/password

col object_name for a30
SELECT OBJECT_NAME, OBJECT_TYPE FROM USER_OBJECTS;

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -------------------
TEST0                          TABLE
SYS_C009868                    INDEX
TEST0INDEX                     INDEX

-- or --

4. Use conventional export/import (exp/imp) (not recommended).

Do you want to learn Oracle Database for Beginners, then read the following articles.

Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )