Solved

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

Posted on 2006-06-21
5
9,791 Views
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:
 "CREATE TYPE "PROCESSDEFINITIONRESULT" TIMESTAMP '2005-12-02:17:33:22' OID '"
 "D09AF9E0D3A64B44944F64F86147A8DF'                   AS"
 "    OBJECT ( "OBJECTIVE" VARCHAR2(500), "RESULT" VARCHAR2(500),"
 "    "OWNER" VARCHAR2(750), "MANAGER" VARCHAR2(750), "LOCATION""
 "    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!
0
Comment
Question by:c_law
  • 2
5 Comments
 
LVL 47

Accepted Solution

by:
schwertner earned 500 total points
Comment Utility
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.

Workarounds:

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"
    schema!

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;
         begin
           open c1;
           loop
             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));
             commit;
           end loop;
           close c1;
         end;
         /

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

Author Comment

by:c_law
Comment Utility
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.
0
 

Author Comment

by:c_law
Comment Utility
This can be closed - turns out someone had imported the objects a month previously...
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now