Re: Not my type- copying user defined types.- HELP!
- From: "Howard Latham" <howard.latham@xxxxxxxxx>
- To: TESTAJ3@xxxxxxxxxxxxxx
- Date: Tue, 18 Nov 2008 12:44:28 +0000
Thanks - yes thats a useful article but are trying to copy from a LIVE
schema to a test SChema so we cannot drop the from schema or tinker with the
type. I am just testing an imp parameter TOID_NOVALIDATE which might solve
it.
2008/11/18 <TESTAJ3@xxxxxxxxxxxxxx>
>
> Howard here is what I found in metalstink :)
>
> Subject:
> *IMP-17 IMP-3 ORA-2304 IMP-63 FROMUSER/TOUSER Import of Table With Object
> Column* Doc ID:
> *NOTE:1066139.6* Type:
> *BULLETIN* Last Revision Date:
> *12-MAR-2003* Status:
> *PUBLISHED*
>
>
> IMP-17 IMP-3 ORA-2304 IMP-63 FROMUSER/TOUSER IMPORT OF TABLE WITH OBJECT
> COLUMN
> ===============================================================================
>
>
>
> Introduction:
> =============
>
> If you are importing using the FROMUSER/TOUSER clause to duplicate a schema
>
> within an instance, you may experience the following errors:
>
> imp system/manager fromuser=a touser=b file=demo.dmp log=import.log
>
> IMP-00017: following statement failed with ORACLE error 2304:
> IMP-00003: ORACLE error 2304 encountered
> ORA-02304: invalid object identifier literal
> IMP-00063: Warning: Skipping table "x"."x" because object
> type "x"."x" cannot be created or has different identifier
>
> 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.
>
> So what are the options available to us for completing this import?
>
>
> Possible Solution Scenarios:
> ============================
>
> 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;
>
>
> Summmary:
> =========
>
> In summary, if FROMUSER/TOUSER import is used to duplicate a schema in an
> instance then object types should be isolated in a schema designated only
> for
> object types. This is a design and maintenance issue that requires serious
> consideration. IGNORE=Y only ignores CREATE TYPE import errors if the
> import
> schema is the export schema.
>
> Note: A table level export/import works exactly the same as a schema level
> in
> regards to object types since the object type is a component of the
> table
> scope.
>
>
> References:
> ===========
>
> Oracle8 Server Utilities: "Considerations for Importing Database Objects
>
>
> .
> _______________________________________
> Joe Testa, Oracle Certified Professional
> Senior Consultant
> Nationwide Investments
> Data Engineering and Administration
>
> (Work) 614-677-1668
> 614-312-6715
>
>
>
> From: "Howard Latham" <howard.latham@xxxxxxxxx> To: ORACLE-L <
> oracle-l@xxxxxxxxxxxxx> Date: 11/18/2008 05:33 AM Subject: Not my type-
> copying user defined types.- HELP! Sent by: oracle-l-bounce@xxxxxxxxxxxxx
> ------------------------------
>
>
>
> Version 10g
>
> I am trying to copy 5 tables with user defined types from one schema to
> another.
> I have used export.
> ON running the import first I got an error saying the OID was already in
> use so the type couldn't be created
> Then I created the types manually in the new schema. NOW import says the
> OIDs don't match
> Any idea how I can copy one schema to another with user defined types?
>
> --
> Howard A. Latham
>
>
>
>
--
Howard A. Latham
Other related posts:
