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

Admin — Tue, 29 Dec 2009 15:06:01 GMT

Hi,

When I was expording a schema and importing it to another schema in the same database i was getting the following ora error import message:

Import: Release 10.2.0.4.0 - 64bit Production on Tuesday, 29 December, 2009 14:05:41

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_02" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_02": parfile=/oracle/dba/scripts/imp_daily_TABLES.par directory=DAILY_TABLES_REFRESH dumpfile=james_expdp_%U.dmp logfile=daily_refresh_impdp.log REMAP_SCHEMA=JAMES:SCOTT TABLE_EXISTS_ACTION=SKIP
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"SCOTT" already exists
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/passWORD_HISTORY
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
ORA-39083: Object type TYPE failed to create with error:
ORA-02304: invalid object identifier literal
Failing sql is:
CREATE TYPE "SCOTT"."SYS_PLSQL_210109_DUMMY_1" OID '6E6497DF899A51E7E0440003BA2F18B2' as table of number;
ORA-39083: Object type TYPE failed to create with error:
ORA-02304: invalid object identifier literal
Failing sql is:
CREATE TYPE "SCOTT"."SYS_PLSQL_210109_9_1" OID '6E6497DF899451E7E0440003BA2F18B2' as object (CUSTID CHAR(25),
PARTYID CHAR(4),
RATINGEQUIVALENCY NUMBER(3));
ORA-39082: Object type TYPE:"SCOTT"."SYS_PLSQL_11945_34_1" created with compilation warnings
ORA-39082: Object type TYPE:"SCOTT"."SYS_PLSQL_11945_34_1" created with compilation warnings
ORA-39083: Object type TYPE failed to create with error:
ORA-02304: invalid object identifier literal
Failing sql is:
CREATE TYPE "SCOTT"."SYS_PLSQL_210109_34_1" OID '6E6497DF899E51E7E0440003BA2F18B2' as table of SENTRY."SYS_PLSQL_210109_9_1";
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/CLUSTER/CLUSTER
Processing object type SCHEMA_EXPORT/CLUSTER/INDEX
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

I couldn't figure it out at first. This was my par file / example of my impdp statement:

impdp parfile=/oracle/dba/scripts/imp_daily.par directory=DAILY_TABLES_REFRESH dumpfile=james_expdp_%U.dmp logfile=daily_refresh_impdp.log REMAP_SCHEMA=JAMES:SCOTT TABLE_EXISTS_ACTION=SKIP PARALLEL=5

It was related to the type. I couldn't figure it out untill i saw someone metioned something about TRANSFORM option. I read it up again in metalink and it worked. Here is what I did:

impdp parfile=/oracle/dba/scripts/imp_daily.par directory=DAILY_TABLES_REFRESH dumpfile=james_expdp_%U.dmp logfile=daily_refresh_impdp.log REMAP_SCHEMA=JAMES:SCOTT TABLE_EXISTS_ACTION=APPEND PARALLEL=5 TRANSFORM=oid:n

I highlighted what i did in red. I changed it to APPEND because I was dropping the user before importing it so it was ok. The TRANSFORM option will take the existing OID and assign it a new id when you provide it with :n option. I hope this helps. This option is only available in 10gR2.

Here is some info from oracle:

Hope it helps:

Applies to:

Enterprise Manager for RDBMS - Version: 10.1.0 to 10.2.0
Oracle Server - Enterprise Edition - Version: 10.1.0 to 10.2.0
Oracle Server - Personal Edition - Version: 10.1.0 to 10.2.0
Oracle Server - Standard Edition - Version: 10.1.0 to 10.2.0
This problem can occur on any platform.

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 Oracle10g 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

The object types MY_COLTYPE and MY_TABTYPE already exist in the source schema U1. When the types are exported, we also export the object_identifier (OID) of the types. Within the current archictecture, the object-identifier needs to be unique in the database.

During import (into the same database), we try to create the same object types in the U2 schema. As the OID of the types already exists in the source schema U1, the types cannot be created in the target schema U2. Import will fail due to:

ORA-02304: invalid object identifier literal

Solution

1. 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. Example:

CONNECT u2/u2
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;

impdp system/manager DIRECTORY=my_dir DUMPFILE=expdp_u1.dmp LOGFILE=impdp_u1.log
REMAP_SCHEMA=u1:u2 TABLE_EXISTS_ACTION=append

...
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
ORA-31684: Object type TYPE:"U2"."MY_COLTYPE" already exists
ORA-31684: Object type TYPE:"U2"."MY_TABTYPE" already exists
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "U2"."MY_OBJTAB"                         6.132 KB       1 rows
. . imported "U2"."MY_RELTAB"                         6.226 KB       1 rows
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 3 error(s) at 13:13

Note that in Oracle10g Release 2, this will fail for the table MY_RELTAB with the errors:

...
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
ORA-31684: Object type TYPE:"U2"."MY_COLTYPE" already exists
ORA-31684: Object type TYPE:"U2"."MY_TABTYPE" already exists
Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39152: Table "U2"."MY_RELTAB" exists. Data will be appended to existing table
but all dependent metadata will be skipped due to table_exists_action of append
ORA-39152: Table "U2"."MY_OBJTAB" exists. Data will be appended to existing table
but all dependent metadata will be skipped due to table_exists_action of append
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "U2"."MY_OBJTAB"                         6.125 KB       1 rows
ORA-31693: Table data object "U2"."MY_RELTAB" failed to load/unload and is being skipped 
due to error:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
ORA-39779: type "U2"."MY_COLTYPE" not found or conversion to latest version is not possible
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 6 error(s) at 12:39:54

2. In Oracle10g Release 2 (10.2.0.x.y), there is no need to pre-create the object types and the tables in the target schema. Instead, 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. Example:

impdp system/manager DIRECTORY=my_dir DUMPFILE=expdp_u1.dmp LOGFILE=impdp_u1.log
REMAP_SCHEMA=u1:u2 TRANSFORM=oid:n

...
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "U2"."MY_OBJTAB"                         6.125 KB       1 rows
. . imported "U2"."MY_RELTAB"                         6.218 KB       1 rows
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 12:38:56

Note that the remaining error is an ORA-31684 "Object type USER:"U2" already exists", which was reported earlier at the import. This error is not related. The ORA-31684 can be ignored if the target schema already exists in the target database.

Also note that the transform name OID for the Import DataPump parameter TRANSFORM has been introduced in Oracle10g Release 2 (10.2.0.x.y). If you use this value in Oracle10g Release 1, the Import DataPump job will fail with:

...
ORA-39001: invalid argument value
ORA-39042: invalid transform name OID

Remark 1: There are certain schema references that Import DataPump cannot find when the REMAP_SCHEMA parameter has been specified. For example, Import DataPump won't find schema references embedded within the body of definitions of types, views, procedures, and packages. Example: User U1 has created the following types:

CREATE TYPE my_vartype AS OBJECT (nr NUMBER)
/
CREATE TYPE my_var AS VARRAY (10000) OF u1.my_vartype
/
CREATE TYPE my_type2 AS OBJECT (nr NUMBER, col2 u1.my_vartype)
/
CREATE TABLE my_objtab2 OF u1.my_type2
/

Note that the type bodies of types MY_VAR and MY_TYPE2 have a reference to the schema name 'U1'. Import DataPump REMAP_SCHEMA won't be able to replace the schema name 'U1' within the type body, and the import logfile will show the errors:

ORA-39082: Object type TYPE:"U2"."MY_VAR" created with compilation warnings
ORA-39082: Object type TYPE:"U2"."MY_TYPE2" created with compilation warnings
ORA-39117: Type needed to create table is not included in this operation. Failing sql is:
CREATE TABLE "U2"."MY_OBJTAB2" OF "U2"."MY_TYPE2" ...

These errors are expected behavior because the type bodies contain an explicit reference to a schema name. Possible solutions:
1. Do not explicitly specify the schema name of the owner in the type body if this is the same owner who has created the type and if you intend to transfer the data to a different schema (e.g. REMAP_SCHEMA during Import DataPump). E.g.:

CONNECT u1/u1
CREATE TYPE my_vartype AS OBJECT (nr NUMBER)
/
CREATE TYPE my_var AS VARRAY (10000) OF my_vartype
/
CREATE TYPE my_type2 AS OBJECT (nr NUMBER, col2 my_vartype)
/
CREATE TABLE my_objtab2 OF my_type2
/

or:

2. Prior to Import DataPump have user u1 grant the execute privilege on the type that is referenced:

CONNECT u1/u1
GRANT execute ON my_vartype TO u2;

Remark 2: With the original export and import clients, a similar parameter is the TOID_NOVALIDATE parameter. If you have pre-created the object types and tables in the target schemas U2, you can import with:

imp system/manager FILE=exp_u1.dmp LOG=imp_u1.log FROMUSER=u1 TOUSER=u2 IGNORE=y 
TOID_NOVALIDATE=u2.my_coltype, u2.my_tabtype

The TOID_NOVALIDATE parameter cannot be used to skip validation of object type tables. Therefore the original import will still show the errors:

...
IMP-00071: Object identifier of imported table mismatches object identifier of existing table
 "CREATE TABLE "MY_OBJTAB" OF "MY_TABTYPE" OID '358A184D9B2E4896907E8D7A1902B"
 "F77' OIDINDEX (PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FRE"
 "ELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS")  PCTFRE"
 "E 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 F"
 "REELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS"
. . importing table                    "MY_RELTAB"          1 rows imported
Import terminated successfully with warnings.

This is expected behavior. For workarounds, see:
Note 1066139.6 "IMP-17 IMP-3 ORA-2304 IMP-63 FROMUSER/TOUSER Import of Table With Object Column"

References

BUG:2978711 - Allow Multiple User Imports To Succeed With User Defined Datatypes
BUG:4156453 - When Import Xmltype Table Into Another User, Ora-2304 Occurs
NOTE:1066139.6 - IMP-17 IMP-3 ORA-2304 IMP-63 FROMUSER/TOUSER Import of Table With Object Column