DataPump Import Of Object Types Fails With Errors ORA-39083 ORA-2304 Or ORA-39117 ORA-39779 (Doc ID 351519.1)

Last updated on JANUARY 16, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 10.1.0.2 to 11.2.0.3 [Release 10.1 to 11.2]
Oracle Database - Enterprise Edition - Version 12.1.0.2 to 12.1.0.2 [Release 12.1]
Information in this document applies to any platform.
***Checked for relevance on 05-Feb-2014***

Symptoms

The example below is based on the directory object my_dir that refers to an existing directory on the server where the Oracle RDBMS is installed. Example:

Windows: CONNECT system/manager
CREATE OR REPLACE DIRECTORY my_dir AS 'D:\export';
GRANT read, write ON DIRECTORY my_dir TO public;
Unix: CONNECT system/manager
CREATE OR REPLACE DIRECTORY my_dir AS '/home/users/export';
GRANT read, write ON DIRECTORY my_dir TO public;


A schema that owns object types needs to be duplicated from schema U1 to schema U2 in the same database. Example:

CONNECT system/manager
CREATE USER u1 IDENTIFIED BY u1 DEFAULT TABLESPACE users;
CREATE USER u2 IDENTIFIED BY u2 DEFAULT TABLESPACE users;
ALTER USER u1 QUOTA UNLIMITED ON users;
ALTER USER u2 QUOTA UNLIMITED ON users;
GRANT create session, create table, create type TO u1;
GRANT create session, create table, create type TO u2;
CONNECT u1/u1
CREATE TYPE my_coltype AS OBJECT (nr NUMBER, txt VARCHAR2(10))
/
CREATE TYPE my_tabtype AS OBJECT (nr NUMBER, txt VARCHAR2(10))
/
CREATE TABLE my_reltab (nr NUMBER, col1 my_coltype);
CREATE TABLE my_objtab OF my_tabtype;
INSERT INTO my_reltab VALUES (1, my_coltype(1, 'Line 1'));
INSERT INTO my_objtab VALUES (1, 'Line 1');
COMMIT;


The schema U1 can be exported successfully. E.g.:

expdp system/manager DIRECTORY=my_dir DUMPFILE=expdp_u1.dmp LOGFILE=expdp_u1.log SCHEMAS=u1


The import into schema U2 fails though with the following errors:

impdp system/manager DIRECTORY=my_dir DUMPFILE=expdp_u1.dmp LOGFILE=impdp_u1.log 
REMAP_SCHEMA=u1:u2
...
ORA-39083: Object type TYPE failed to create with error:
ORA-02304: invalid object identifier literal
Failing sql is:
CREATE TYPE "U2"."MY_COLTYPE"   OID 'DDB334945FA24A41AC0099E457715B62' as ...
ORA-39083: Object type TYPE failed to create with error:
ORA-02304: invalid object identifier literal
Failing sql is:
CREATE TYPE "U2"."MY_TABTYPE"   OID 'EEC16EAE6DF34B4FA755DBB448EC4F78' as ...
Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39117: Type needed to create table is not included in this operation. Failing sql is:
CREATE TABLE "U2"."MY_RELTAB" ...
ORA-39117: Type needed to create table is not included in this operation. Failing sql is:
CREATE TABLE "U2"."MY_OBJTAB" OF "U2"."MY_TABTYPE"  ...
...


Similar errors with the original export and import utilities are:

...
IMP-00017: following statement failed with ORACLE error 2304:
 "CREATE TYPE "MY_COLTYPE" TIMESTAMP '2006-01-04:12:36:27' OID 'F1C8AAABCB114"
 "6B0B2672663397B0156'   as object (nr number, txt varchar2(10))"
IMP-00003: ORACLE error 2304 encountered
ORA-02304: invalid object identifier literal
IMP-00017: following statement failed with ORACLE error 2304:
 "CREATE TYPE "MY_TABTYPE" TIMESTAMP '2006-01-04:12:36:28' OID '317DF88365654"
 "C6784C17B0FE14C4610'   as object (nr number, txt varchar2(10))"
IMP-00003: ORACLE error 2304 encountered
ORA-02304: invalid object identifier literal
IMP-00063: Warning: Skipping table "U2"."MY_OBJTAB" because object type "U2"."MY_TABTYPE"
cannot be created or has different identifier
IMP-00063: Warning: Skipping table "U2"."MY_RELTAB" because object type "U2"."MY_COLTYPE"
cannot be created or has different identifier
Import terminated successfully with warnings.
...

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms