数据泵导入问题解决方法

1、网页地址:IMP同库Type对象导入报错ORA-02304（续） 2012-06-11 21:27:02 分类： Linux 在上篇IMP同库Type对象导入报错ORA-02304（ 当我们使用exp/imp的时候，报错ORA-02304实际上是没有什么特别好的解决方法的。Type导入相同库报错的本质在于在导出的时候，Oracle会将type的oid连带导出。而导入的时候，又希望将其还原为相同的oid从而引发冲突。 那么，是不是我们就没有办法了呢？我们借助Oracle 10g提出的数据泵（Data Dump）工具，是可以避免这个问题的。 1、环境准备&#

2、160;我们同样适用Oracle 11gR2进行试验。  SQL> select * from v$version;BANNER-Oracle Database 11g Enterprise Edition Release .0 - ProductionPL/SQL Release .0 - ProductionCORE.0Production  在scott用户下，我们创建一些type类型对象。  SQL> grant imp_full_database to scott;

3、Grant succeeded SQL> grant exp_full_database to scott;Grant succeeded SQL> conn scott/tigerwilson;Connected to Oracle Database 11g Enterprise Edition Release .0 Connected as scott SQL> create type mt_type as object (xm number, tchar varchar2(10); 2 / Type created

4、 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 

5、; 之后，我们创建用户scottback。使用数据泵expdp从scott中将数据导出。  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 suc

6、ceeded SQL> grant imp_full_database to scottback;Grant succeeded  2、expdp数据导出 数据泵DataDump作为10g中推出的新一代数据备份还原工具，具有很多好的特点。DataDump是服务器端使用工具，需要在服务器上执行。 首先，我们需要创建directory对象，对应服务器上的一个目录位置。  rootoracle11g /# pwd/rootoracle11g /# mkdir exportrootoracle11g /# ls -l | gr

7、ep exportdrwxr-xr-x 2 root root 4096 Jun 11 19:29 exportrootoracle11g /# chown oracle:oinstall exportrootoracle11g /# ls -l | grep exportdrwxr-xr-x 2 oracle oinstall 4096 Jun 11 19:39 export  创建directory对象，并且将read write权限授予给scott和scottback。  SQL> create or replace directory MY

8、_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命令行进行导出。  oracleoracle11g $ cd /export/oracleoracle11g export$ p

9、wd/exportoracleoracle11g export$ expdp scott/tigerwilson directory=my_dir dumpfile=scott.dmp logfile=resexp.log schemas=scott Export: Release .0 - Production on Mon Jun 11 19:35:08 2012 oracleoracle11g export$ expdp scott/tigerwilson directory=my_dir dumpfile=scott.dmp logfile=rese

10、xp.log schemas=scottExport: Release .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 .0 - ProductionWith the Partitioning, OLAP, Data Mining

11、and Real Application Testing optionsStarting "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 &

12、quot;SCOTT"."T" 0 KB 0 rows. . exported "SCOTT"."T1" 0 KB 0 rows. . exported "SCOTT"."T2" 0 KB 0 rowsMaster table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded*Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is: /e

13、xport/scott.dmpJob "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 19:36:00 oracleoracle11g export$ ls -ltotal 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  3、impdp导入数据 在默认的impdp方式下，t

14、ype也是不能导入到相同的数据库中去的。  oracleoracle11g export$ impdp scottback/scottbackwilson directory=my_dir dumpfile=scott.dmp logfile=resimp.log remap_schema=scott:scottback Import: Release .0 - Production on Mon Jun 11 19:37:37 2012 Copyright (c) 1982, 2009, Oracle and/or its affili

15、ates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release .0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsMaster table "SCOTTBACK"."SYS_IMPORT_FULL_01" successfully loaded/unloadedStarting &quo

16、t;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/USERORA-31684: Object type USER:"SCOTTBACK" already existsProcessing object type SCHEMA_EXPORT/SYSTEM

17、_GRANTProcessing object type SCHEMA_EXPORT/ROLE_GRANTProcessing object type SCHEMA_EXPORT/DEFAULT_ROLEProcessing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing object type SCHEMA_EXPORT/TYPE/TYPE_SPECORA-39083: Object type TYPE failed to create with error:ORA-02304: invalid object ide

18、ntifier literalFailing sql is:CREATE TYPE "SCOTTBACK"."MT_TYPE" OID 'C230A55B1FC34E1DE040A8C0580017C6' as object (xm number, tchar varchar2(10);  Processing object type SCHEMA_EXPORT/TABLE/TABLEORA-39117: Type needed to create table is not included in this opera

19、tion. 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

20、 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_T

21、EST" 22.90 KB 1 rows（篇幅原因，省略部分 .）Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSJob "SCOTTBACK"."SYS_IMPORT_FULL_01" completed with 4 error(s) at 19:37:47  注意，在默认情况下，我们导入数据依然会遇到oid的问题。显示的依然是type创建SQL中包括有oid信息，引起oid冲突。进而是连带的数据表my_tabletype不能创建

22、。 有一个片段可以关注：  CREATE TYPE "SCOTTBACK"."MT_TYPE" OID 'C230A55B1FC34E1DE040A8C0580017C6' as object (xm number, tchar varchar2(10);  Processing object type SCHEMA_EXPORT/TABLE/TABLEORA-39117: Type needed to create table is not included in this operat

23、ion. 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相同，说明type的oid在Oracle

24、内部是作为关联的重要信息使用的。 在impdp中，我们可以使用transform参数设置，要求将原有dmp文件中oid映射重新生成。  oracleoracle11g export$ impdp scottback/scottbackwilson directory=my_dir dumpfile=scott.dmp logfile=resimp2.log remap_schema=scott:scottback transform=oid:n  Import: Release .0 - Production on Mon Jun 11

25、19:39:07 2012 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release .0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsMaster table "SCOTTBACK".&qu

26、ot;SYS_IMPORT_FULL_01" successfully loaded/unloadedStarting "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/USERORA-31684: Object typ

27、e USER:"SCOTTBACK" already existsProcessing object type SCHEMA_EXPORT/SYSTEM_GRANTProcessing object type SCHEMA_EXPORT/ROLE_GRANTProcessing object type SCHEMA_EXPORT/DEFAULT_ROLEProcessing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing object type SCHEMA_EXPORT/TYPE/TYPE_SPE

28、CProcessing object type SCHEMA_EXPORT/TABLE/TABLEProcessing 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.99

29、2 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 rowsPr