怎么向oracle内导入数据类型,ORACLE 自定义类型该怎么导入

当前位置:我的异常网» 数据库 » ORACLE 自定义类型该怎么导入

ORACLE 自定义类型该怎么导入

www.myexceptions.net  网友分享于：2013-08-25  浏览：49次

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

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

文章评论