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

Last updated on MAY 26, 2023

Applies to:

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:

CONNECT system/password
CREATE OR REPLACE DIRECTORY my_dir AS '<PATH>';
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/password
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/password 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/password 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.
...

Changes

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.