DataPump Import Of Object Types Fails With Errors ORA-39083 ORA-2304 Or ORA-39117 ORA-39779

<<Back to Oracle DATAPUMP Main Page

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

Import: Release 12.1.0.2.0 - Production on Tue May 29 07:59:12 2018
Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Advanced Analytics and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/********@TEST_QA parfile=import_TEST.par
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
ORA-39083: Object type TYPE:"TEST_QA01"."LOG_RECORD" failed to create with error:
ORA-02304: invalid object identifier literal
Failing sql is:
CREATE EDITIONABLE TYPE "TEST_QA01"."LOG_RECORD"   OID '3D90A5F990BE773EF0531103830A52A8' as object(
        code varchar2(250 char),
        words varchar2(4000 char),
        component varchar2(1000 char),
        severity number(1,0),
        contextTypes varchar2(4000 char),
        contextIds varchar2(1000 char),
        time timestamp(6),
        userName varchar2(250 char),
        stationName varchar2(1
ORA-39083: Object type TYPE:"TEST_QA01"."TXPRDATE" failed to create with error:
ORA-02304: invalid object identifier literal
Failing sql is:
CREATE EDITIONABLE TYPE "TEST_QA01"."TXPRDATE"   OID '3D90A5F990D8883FE0441103830B52A8' as object(
        Prohibited integer(1),
        FamilyID varchar2(50),
        KeyVal varchar2(4000)
);

CAUSE

Trying to import an object of type "type" with the same OID already present in the database.eg
The object types "LOG_RECORD" already exist in the source schema TEST_QA02. When the types are exported, oracle also export the object_identifier (OID) of the types. Within the current architecture, the object-identifier needs to be unique in the database.
During import (into the same database "I used remap_schema to duplicate the schema TEST_QA02 as TEST_QA01 in the same PDB"), oracle try to create the same object types in the TEST_QA01 schema. As the OID of the types already exists in the source schema TEST_QA02, the types cannot be created in the target schema TEST_QA01. resulting Import will fail due to:
ORA-02304: invalid object identifier literal.

Solution:

You can use the Import DataPump parameter TRANSFORM which enables you to alter object creation DDL for the types. The value N (= No) for the transform name OID implies that a new OID is assigned.
I modified the Import pafile like below to resolve the issue and it worked.

directory=ASM_DUMP
dumpfile=TEST_EXP_1_QA02%U.dmp
logfile=EXT:TEST_1_QA02_import.log
remap_schema=TEST_QA02:TEST_QA01
parallel=8
TRANSFORM=oid:n

NOTE:-In Oracle10g Release 1 (10.1.0.x.y), you can pre-create the object types and the tables in the target schema, and run an import with the TABLE_EXISTS_ACTION parameter.

For more detail check oracle support note Doc ID 351519.1