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.