ORA-39083: Object type TYPE:"AURTO"."NORES_DATA" failed to create with error: ORA-02304: invalid object identifier literal

ORA-39083: Object type TYPE:"AURTO"."NORES_DATA" failed to create with error:
ORA-02304: invalid object identifier literal



Command executed:

impdp  SCHEMAS=PORTO remap_schema=PORTO:AURTO remap_tablespace=PORTO:AURTO directory=PRODIMP dumpfile=PROD_%U.dmp logfile=PORTO_imp.log parallel=5

Error received:

ORA-39083: Object type TYPE:"AURTO"."NORES_DATA" failed to create with error:
ORA-02304: invalid object identifier literal

Failing sql is:
CREATE EDITIONABLE TYPE "AURTO"."NORES_DATA"   OID '6EE61264420D2234E04400144FEDB040' AS OBJECT (
  VAL              VARCHAR2(200)
)
ORA-39083: Object type TYPE:"AURTO"."NORES_TABLE_TYPE" failed to create with error:
ORA-02304: invalid object identifier literal

Failing sql is:

CREATE EDITIONABLE TYPE "AURTO"."NORES_TABLE_TYPE"   OID '6EE6126442112234E04400144FEDB930' AS TABLE OF NORES_data


Cause:

Trying to create an object of type "TYPE" with the same OID which is already present in the database.

Here i was trying to import schema AURTO from schema PORTO and both schema belongs to same db.

So Object NORES_DATA already exist in source schema with the OID which it is trying to use to create in target schema.

while you import TYPE from once schema to another it's OID (object identifier) also exported/imported.

In a database OID must be unique but during import IMPDP uses the same DDL and OID remains same while creating this type.

hence it fails.

SOLUTION:

In order to fix this, you can use one parameter along with your impdp statement which is TRANSFORM.

TRANSFORM enables you to alter the DDL for the type and hence a new OID is assigned.

The value N (=NO) for this parameter implies that new OID will be assigned.

impdp  SCHEMAS=PORTO remap_schema=PORTO:AURTO remap_tablespace=PORTO:AURTO directory=PRODIMP dumpfile=PROD_%U.dmp logfile=PORTO_imp.log TRANSFORM=oid:n Parallel=5

Thanks & Regards

Nikhil Kumar.