ITPub博客

首页 > Linux操作系统 > Linux操作系统 > IMP同库Type对象导入报错ORA-02304(续)

IMP同库Type对象导入报错ORA-02304(续)

原创 Linux操作系统 作者:realkid4 时间:2012-06-11 21:27:02 0 删除 编辑

 

在上篇《IMP同库Type对象导入报错ORA-02304》(http://space.itpub.net/17203031/viewspace-732089)中,我们讨论了由于type使用特性的原因,如果我们使用exp/imp工具导入到相同数据库中,是会发生报错现象。

 

当我们使用exp/imp的时候,报错ORA-02304实际上是没有什么特别好的解决方法的。Type导入相同库报错的本质在于在导出的时候,Oracle会将typeoid连带导出。而导入的时候,又希望将其还原为相同的oid从而引发冲突。

 

那么,是不是我们就没有办法了呢?我们借助Oracle 10g提出的数据泵(Data Dump)工具,是可以避免这个问题的。

 

1、环境准备

 

我们同样适用Oracle 11gR2进行试验。

 

 

SQL> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

PL/SQL Release 11.2.0.1.0 - Production

CORE        11.2.0.1.0         Production

 

 

scott用户下,我们创建一些type类型对象。

 

 

SQL> grant imp_full_database to scott;

Grant succeeded

 

SQL> grant exp_full_database to scott;

Grant succeeded

 

SQL> conn scott/tiger@wilson;

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

Connected as scott

 

SQL> create type mt_type as object (xm number, tchar varchar2(10));

  2  /

 

Type created

 

SQL> select type_name, type_oid from user_types;

TYPE_NAME                      TYPE_OID

------------------------------ --------------------------------

MT_TYPE                        C230A55B1FC34E1DE040A8C0580017C6

 

SQL> create table my_tabletype of mt_type;

Table created

 

SQL> insert into my_tabletype values (1,'df');

1 row inserted

 

SQL> commit;

Commit complete

 

 

之后,我们创建用户scottback。使用数据泵expdpscott中将数据导出。

 

 

SQL> create user scottback identified by scottback;

User created

 

SQL> grant resource to scottback;

Grant succeeded

 

SQL> grant connect to scottback;

Grant succeeded

 

SQL> grant exp_full_database to scottback;

Grant succeeded

 

SQL> grant imp_full_database to scottback;

Grant succeeded

 

 

2expdp数据导出

 

数据泵DataDump作为10g中推出的新一代数据备份还原工具,具有很多好的特点。DataDump是服务器端使用工具,需要在服务器上执行。

 

首先,我们需要创建directory对象,对应服务器上的一个目录位置。

 

 

[root@oracle11g /]# pwd

/

[root@oracle11g /]# mkdir export

[root@oracle11g /]# ls -l | grep export

drwxr-xr-x    2 root   root      4096 Jun 11 19:29 export

[root@oracle11g /]# chown oracle:oinstall export

[root@oracle11g /]# ls -l | grep export

drwxr-xr-x   2 oracle oinstall  4096 Jun 11 19:39 export

 

 

创建directory对象,并且将read write权限授予给scottscottback

 

 

SQL> create or replace directory MY_DIR

  2    as '/export';

 

Directory created

 

SQL> grant write, read on directory my_dir to scott;

Grant succeeded

 

SQL> grant write, read on directory my_dir to scottback;

Grant succeeded’

 

 

再使用expdp命令行进行导出。

 

 

[oracle@oracle11g ~]$ cd /export/

[oracle@oracle11g export]$ pwd

/export

[oracle@oracle11g export]$ expdp scott/tiger@wilson directory=my_dir dumpfile=scott.dmp logfile=resexp.log schemas=scott

 

Export: Release 11.2.0.1.0 - Production on Mon Jun 11 19:35:08 2012

 

[oracle@oracle11g export]$ expdp scott/tiger@wilson directory=my_dir dumpfile=scott.dmp logfile=resexp.log schemas=scott

Export: Release 11.2.0.1.0 - Production on Mon Jun 11 19:35:08 2012

 

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SCOTT"."SYS_EXPORT_SCHEMA_01":  scott/********@wilson directory=my_dir dumpfile=scott.dmp logfile=resexp.log schemas=scott

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

(篇幅原因,部分省略…..

. . exported "SCOTT"."T"                                     0 KB       0 rows

. . exported "SCOTT"."T1"                                    0 KB       0 rows

. . exported "SCOTT"."T2"                                    0 KB       0 rows

Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:

  /export/scott.dmp

Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 19:36:00

 

[oracle@oracle11g export]$ ls -l

total 420

-rw-r--r--  1 oracle oinstall   2467 Jun 11 19:36 resexp.log

-rw-r-----  1 oracle oinstall 421888 Jun 11 19:36 scott.dmp

 

 

3impdp导入数据

 

在默认的impdp方式下,type也是不能导入到相同的数据库中去的。

 

 

[oracle@oracle11g export]$ impdp scottback/scottback@wilson directory=my_dir dumpfile=scott.dmp logfile=resimp.log remap_schema=scott:scottback

 

Import: Release 11.2.0.1.0 - Production on Mon Jun 11 19:37:37 2012

 

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table "SCOTTBACK"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "SCOTTBACK"."SYS_IMPORT_FULL_01":  scottback/********@wilson directory=my_dir dumpfile=scott.dmp logfile=resimp.log remap_schema=scott:scottback

Processing object type SCHEMA_EXPORT/USER

ORA-31684: Object type USER:"SCOTTBACK" already exists

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC

ORA-39083: Object type TYPE failed to create with error:

ORA-02304: invalid object identifier literal

Failing sql is:

CREATE TYPE "SCOTTBACK"."MT_TYPE"   OID 'C230A55B1FC34E1DE040A8C0580017C6' as object (xm number, tchar varchar2(10));

 

 

Processing object type SCHEMA_EXPORT/TABLE/TABLE

ORA-39117: Type needed to create table is not included in this operation. Failing sql is:

CREATE TABLE "SCOTTBACK"."MY_TABLETYPE" OF "SCOTTBACK"."MT_TYPE"  OID 'C230B8AA21E527C9E040A8C058001816' OIDINDEX  ( PCTFREE 10 INITRANS 2 MAXTRANS 255  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "SYSTEM" ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

. . imported "SCOTTBACK"."BASELINE_TEST"                 22.90 KB       1 rows

(篇幅原因,省略部分 …..

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Job "SCOTTBACK"."SYS_IMPORT_FULL_01" completed with 4 error(s) at 19:37:47

 

 

注意,在默认情况下,我们导入数据依然会遇到oid的问题。显示的依然是type创建SQL中包括有oid信息,引起oid冲突。进而是连带的数据表my_tabletype不能创建。

 

有一个片段可以关注:

 

 

CREATE TYPE "SCOTTBACK"."MT_TYPE"   OID 'C230A55B1FC34E1DE040A8C0580017C6' as object (xm number, tchar varchar2(10));

 

 

Processing object type SCHEMA_EXPORT/TABLE/TABLE

ORA-39117: Type needed to create table is not included in this operation. Failing sql is:

CREATE TABLE "SCOTTBACK"."MY_TABLETYPE" OF "SCOTTBACK"."MT_TYPE"  OID 'C230B8AA21E527C9E040A8C058001816' OIDINDEX  ( PCTFREE 10 INITRANS 2 MAXTRANS 255  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS

 

 

关联oid相同,说明typeoidOracle内部是作为关联的重要信息使用的。

 

impdp中,我们可以使用transform参数设置,要求将原有dmp文件中oid映射重新生成。

 

 

[oracle@oracle11g export]$ impdp scottback/scottback@wilson directory=my_dir dumpfile=scott.dmp logfile=resimp2.log remap_schema=scott:scottback transform=oid:n

 

Import: Release 11.2.0.1.0 - Production on Mon Jun 11 19:39:07 2012

 

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table "SCOTTBACK"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "SCOTTBACK"."SYS_IMPORT_FULL_01":  scottback/********@wilson directory=my_dir dumpfile=scott.dmp logfile=resimp2.log remap_schema=scott:scottback transform=oid:n

Processing object type SCHEMA_EXPORT/USER

ORA-31684: Object type USER:"SCOTTBACK" already exists

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

. . imported "SCOTTBACK"."BASELINE_TEST"                 22.90 KB       1 rows

. . imported "SCOTTBACK"."DEPT"                          5.937 KB       4 rows

. . imported "SCOTTBACK"."EMP"                           8.992 KB      14 rows

. . imported "SCOTTBACK"."MY_TABLETYPE"                  6.507 KB       1 rows

. . imported "SCOTTBACK"."SALES_QUAL"                    6.007 KB       6 rows

. . imported "SCOTTBACK"."SALGRADE"                      5.867 KB       5 rows

. . imported "SCOTTBACK"."BONUS"                             0 KB       0 rows

. . imported "SCOTTBACK"."T"                                 0 KB       0 rows

. . imported "SCOTTBACK"."T1"                                0 KB       0 rows

. . imported "SCOTTBACK"."T2"                                0 KB       0 rows

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/COMMENT

Processing object type SCHEMA_EXPORT/VIEW/VIEW

ORA-31684: Object type VIEW:"SCOTTBACK"."V_T1" already exists

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Job "SCOTTBACK"."SYS_IMPORT_FULL_01" completed with 2 error(s) at 19:39:20

 

 

其中,transform取值oid:n的含义就是对oid信息不进行加载,重新进行生成。数据表取值正确。

 

 

 

SQL> conn scottback/scottback@wilson;

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

Connected as scottback

 

SQL> select * from my_tabletype;

 

        XM TCHAR

---------- ----------

         1 df

 

 

4、结论

 

随着Oracle功能不断完善,很多新特性在exp/imp工具上已经不能支持。Oracle 10g下推出的Data Dump有很多功能,是我们可以进行借鉴使用的。

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/17203031/viewspace-732501/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 配置Yum本地源
请登录后发表评论 登录
全部评论
求道~

注册时间:2010-11-30

  • 博文量
    545
  • 访问量
    7555060