Sunday, February 14, 2010

How to avoid ORA-02304: invalid object identifier literal


Once upon a time i faced an error ORA-02304 while Importing one database schema to another user (Actually i was copying the scheme for some reasons and i had already 1 copy of the same schema in my database).

The complete error was

IMP-00003: ORACLE error 2304 encountered

ORA-02304: invalid object identifier literal

IMP-00063: Warning: Skipping table "DEV_SCHEMA"."SUPPLIER_PAYMENT" 
because object type 

 "DEV_SCHEMA"."SUP_PAYMENT_TYPE" cannot be created or has different 

Whenever you create Oracle object types with command for example

create type mytype as object
(ids number,
 names varchar2(60));

Oracle assigns Object ID internally for all objects while i was imported the same Type with Same Object ID was already exists in my database.
One column in my table SUPPLIER_PAYMENT contains Nested Table type column and in my database i had already one copy of my schema and NESTED TABLE TYPE with same Object ID (Internally) named SUP_PAYMENT_TYPE.

Whenever you create objects with TYPES its a good practice to create all objects types in separate schema so you can share your objects in different schema's objects.

I removed the duplicate schema from the database and re-imported my tables and everything went smoothly.

So, Keep in mind If u going to work with objects in Oracle its better to create them in separate schema.

Have a nice day,


  1. or just use impdb parameter "TRANSFORM=oid:n"

  2. Hi,
    Thanks for comments in which DB version this parameter added ?

    My solution is similar to Oracle support one and for 10g database.

    I guess 11g got this TRANSFORM parameter.

  3. Hi. I've also get this error in 12c, using the Data Pump Import Wizard from Sql Developer. My solution was check the option "Regenerate Object IDs" in the step 4 of 6 from the wizard.