ORACLE 自定义类型该如何导入?

4r4dn5h3js

ORACLE 自定义类型该如何导入?
在某数据库中,用exp导出用户a下的所有数据,包括自定义的类型"MAIN_SZ_ZGY_TYPE".然后用imp将导出的数据导入同一数据库的用户b中,发现表和序列都可以导入,但是自定义的类型导入失败.   

经由直接路径导出由EXPORT:V09.02.00创建的导出文件
已经完成ZHS16GBK字符集和AL16UTF16 NCHAR 字符集中的导入
. 正在将TYXHL_DEV的对象导入到 ZTYXHL_DEV
IMP-00017: 由于 ORACLE 的 2304 错误，以下的语句失败
"CREATE TYPE "MAIN_SZ_ZGY_TYPE" TIMESTAMP '2008-03-07:15:25:13' OID '91234DA"
"113E2469C859AD34D984CB5E1'                                                 "
"                           as object"
"("
"total                        NUMBER,"
"  total_lj_je                  NUMBER,"
"  corresponding_period_je      NUMBER,"
"  corresponding_period_percent VARCHAR2(30)"
")"

IMP-00003: 遇到 ORACLE 错误 2304
ORA-02304: 无效的对象标识文字
IMP-00017: 由于 ORACLE 的 2304 错误，以下的语句失败

请问：ORACLE 自定义类型该如何导入？？？？

出错原因：
往b用户imp表时，要创建type，使用的OID和用户a的一样，同一个实例的OID不能重复。

解决办法：
在system用户下定义 type MAIN_SZ_ZGY_TYPE，授权
grant all on MAIN_SZ_ZGY_TYPE to public;
用户a建表用system.MAIN_SZ_ZGY_TYPE 类型，这样导出用户表时就不会到type了，在用户b中就不会建type。

详细内容如下：
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


本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/E_wsq/archive/2008/11/25/3363784.aspx