Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


IMPORT FAILED: IMP-00003: ORACLE error 2304 encountered, ORA-02304: invalid object identifier literal

Posted on 2006-06-21
Medium Priority
Last Modified: 2008-03-17
We have a number of Oracle databases on a couple of nodes.

We are trying to export 4 schemas from one database to another.

When we import, we get the following errors because of object types in the schemas:

IMP-00003: ORACLE error 2304 encountered
ORA-02304: invalid object identifier literal
IMP-00017: following statement failed with ORACLE error 2304:
 "D09AF9E0D3A64B44944F64F86147A8DF'                   AS"
 "    NUMBER, "[TRIGGER]" VARCHAR2(500))"

I understand that this error occours because the OID is unique to the object, and the import is unable to re-create the objects with the same OID.

PLEASE NOTE: All objects are used ONLY in stored procs. There are no tables (apart from temp tables) that use these objects.

After doing lots of reading online, I understand the following method would allow us to do the import:

1) Move all objects to a new schema.
2) Create synonyms to the new objects and update all exisitng references.
3) Create the 'object schema' in the new database.
4) Import the original schema as normal.

This however is not a good solution for us, as this is a production database and we really don't want to change the schema.

One possible solution we have come up with is as follows. I need to know if this will work:

1) Re-create the schema without any data in the new database from exisitng scrpits (this is trivial to do)
2) Export the data only
3) Import the data only

If we do this, will the PK/FK relationships be maintained, and is there anything that can go wrong or I need to look out for?

We have a final option, but again we are not sure of the implications:

1) Export full schemas
2) Import with 'ignore warnings' - in my understanding, this will just skip the object creation
3) Re-create objects from script (including temp tables that use the objects)
4) Re-compile stored procs that use these objects

Please advise on these 3 methods, and which (if any) are suitable for us.

Many thanks in advance!
Question by:c_law
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
LVL 48

Accepted Solution

schwertner earned 2000 total points
ID: 16950256
These errors will occur if the schema has a user defined object type(s)
(CREATE TYPE) and a relational table column of a user defined datatype.

The IMP-00017 error is of particular interest since it indicates te source
of the error:

  IMP-00017: following statement failed with ORACLE error 2304:
  "CREATE TYPE "xxxx" TIMESTAMP '1999-01-01:12:00:00' OID '####' as object ..."

In brief, if the FROMUSER's object types already exist on the target instance,
errors occur because the object identifiers (OIDs) of the TOUSER's object types
already exist. Within a single database instance, object identifiers (OIDs) must
be unique. As a result, the error causes Import will skip the creation of
relational tables with columns of the pre-existing user defined type.


A.) Use the IGNORE=Y clause on the import

    This WILL NOT succeed since CREATE TYPE errors are only ignored if
    importing into the originating schema, not into a separate "to"

B.) Pre-create the relational table in the TOUSER's schema

    This WILL NOT succeed since the CREATE TYPE statement is present in
    the export file.

C.) Drop the TABLE and TYPE in the FROMUSER schema prior to performing
    the import.

    This WILL succeed. Note that we cannot simply drop
    the type since this will result in an ORA-02303 error as follows:

    ORA-02303: cannot drop or replace a type with type or table dependents

    We must first drop all tables containing the target TYPE, then the TYPE
    itself as follows:

    SQL> drop table mytypetable;
    SQL> drop table mytypetable2;

    SQL> drop type mytype;    

D.) From import.log note down the object id (OID) for the erroring type.
    I.e., the OID '####' of the error.  

    Then run the following statement as dba:

    SQL> select OWNER, TYPE_NAME from dba_types where TYPE_OID='####';

    This statement would give you the owner and the typename for this OID.

    If not needed, drop this type as below:

    SQL>drop type XXX;

    Run the import again.

E.) Perform a cascading drop of the FROMUSER prior to performing the import.

    This WILL succeed since it is essentially the same as option C, only
    far less selective. The syntax is quite simple:

    SQL> drop user myfromuser cascade;

F.) Recreate the TYPE in an independent schema, grant all on the TYPE to PUBLIC,
    create a copy of the TABLE in the FROMUSER schema using this public TYPE,
    copy all the old TABLE into the new TABLE using PL/SQL, and redo the
    export. Subsequently, perform the TOUSER import.

    This WILL succeed since the owner of the TYPE is not involved in the
    export or import operations. As such, the CREATE TYPE statement is
    not issued as a part of the import operation.

    The trick part of this option is recreating the object in question using
    the public TYPE. This can accomplished by following this guide:

    -- create the public type
    SQL> connect system/manager@local
    SQL> create or replace type mytype as object (m1 number, m2 varchar2(20));
    SQL> grant all on mytype to public;

    -- rename the user-type table
    SQL> connect myuser/mypassword@local
    SQL> rename mytypetable to mytypetemp;

    -- create the new public-type table to be corrected
    SQL> create table mytypetable (id number primary key, person system.mytype);

    -- copy the data from the user-type table to the public-type table
    SQL> declare
           v_col1  number;
           v_col2  mytype;
           cursor c1 is
             select * from mytypetemp;
           open c1;
             fetch c1 into v_col1, v_col2;
             exit when c1%notfound;
             insert into mytypetable
               values (v_col1, system.mytype(v_col2.m1, v_col2.m2));
           end loop;
           close c1;

    -- drop the user-type and user-type table
    SQL> drop table mytypetable;
    SQL> drop type mytype;    

Author Comment

ID: 16950394
Thanks for the quick reply. :)

I should state that I understand IMP-00017 andf ORA-2304 pretty well now, since I've been reading up on it.

Your option "C" would work and is the favourite option to us at the moment. This however is not ideal as we need to ultimatly send the database to a client so they can host it instead of us. When we do this we need a "friendly" mechanism, and this would require them (at least) to import our .DMP file and then run some scripts to re-create the object types.

Option "E" doesn't seem quite right to me - we have been trying to import this to another database on another node (i.e. different machine) as this is essentially what will happen when we send the DB to our client. I don't understand why dropping the FROMUSER would allow the objects to be created in the original DB (if we were importing there, which we aren't). Can you explain this to me?

Option "F" is first method I outline in my first post, and while I understand this would work it is far from ideal as this the schema is "fixed", i.e. it is a production DB and changing it's schema would mean we have different schemas at different clients, for this version of our software. Once this particular problem is resolved we will implement this method in the latest version we have, but we can't really go changing previous versions of our schema. I hope that made some sense!

Basically, our clients need to have a hasstle-free method of taking the database dump we send them and restoring it. Option "C" gets close, but it's not going to look good to get our clients to restore the DMP file and then run scripts.

Author Comment

ID: 17270730
This can be closed - turns out someone had imported the objects a month previously...

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question