impdp数据表table导入

平常都是对数据库或schema备份还原的，对特定table导入的事情倒是做得很少！

今天要导入指定table操作时，发现oracle有些个别约束！

不多说了，看示例吧！

错误一：不能同时使用schemas与tables参数

$ impdp hfy121123/kingdee schemas=hfy tables=CT_HSE_CusBaseCode remap_schema=hfy:HFY121123 directory=easbak dumpfile=hfy201211230200.dmp logfile=20121123imp.log

Import: Release 11.2.0.2.0 - Production on Fri Nov 23 16:03:01 2012

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
UDI-00010: multiple job modes requested, schema and tables.

$ 

错误二：CT_HSE_CusBaseCode默认被认为是当前用户的表，所以在导入时需要限定

$ impdp hfy121123/kingdee tables=CT_HSE_CusBaseCode remap_schema=hfy:HFY121123 directory=easbak dumpfile=hfy201211230200.dmp logfile=20121123imp.log

Import: Release 11.2.0.2.0 - Production on Fri Nov 23 15:38:47 2012

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39002: invalid operation
ORA-39166: Object HFY121123.CT_HSE_CUSBASECODE was not found.

$ 

 

错误三：其实严格来说这个算不得错误的，起码在语法上并没有什么问题。

$ impdp hfy121123/kingdee tables=hfy.CT_HSE_CusBaseCode directory=easbak dumpfile=hfy201211230200.dmp logfile=20121123imp.log

Import: Release 11.2.0.2.0 - Production on Fri Nov 23 16:12:28 2012

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "HFY121123"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "HFY121123"."SYS_IMPORT_TABLE_01":  hfy121123/******** tables=hfy.CT_HSE_CusBaseCode directory=easbak dumpfile=hfy201211230200.dmp logfile=20121123imp.log 
Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39151: Table "HFY"."CT_HSE_CUSBASECODE" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
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/STATISTICS/TABLE_STATISTICS
Job "HFY121123"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 16:12:35

$ 

在分析这个问题前，我们先来了解下oracle关于导入导出的一个事实：oracle从数据库导出dmp文件，默认不会改变各对象（如：table、index等）的相关属性（如：schema、tablespace等）。

注：其实我也是后来才明白这个事实的，不过对于oracle在导出dmp文件时能否修改对象（如：table、index等）的属性暂时还不清楚，以后再研究吧。

既然我们已经清楚了这个情况，那出现这个问题也就能理解了：我的用户hfy121123与hfy是在相同数据库实例环境下的操作，所以默认导入的表CT_HSE_CusBaseCode自然所属用户是hfy，所以也就出现表已存在的问题了！

正确：指定了表的所属用户，指定schema从hfy到hfy121123

（如果这是在不同的数据库操作，就要考虑下当前操作的数据库实例下 是否具有原导出表所属表空间 的相同的表空间名的表空间了！这，，，好别扭啊，不知道该如何描述才能表达我的意思~``~）

$ impdp hfy121123/kingdee tables=hfy.CT_HSE_CusBaseCode remap_schema=hfy:HFY121123 directory=easbak dumpfile=hfy201211230200.dmp logfile=20121123imp.log

Import: Release 11.2.0.2.0 - Production on Fri Nov 23 14:10:38 2012

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "HFY121123"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "HFY121123"."SYS_IMPORT_TABLE_01":  hfy121123/******** tables=hfy.CT_HSE_CusBaseCode remap_schema=hfy:HFY121123 directory=easbak dumpfile=hfy201211230200.dmp logfile=20121123imp.log 
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "HFY121123"."CT_HSE_CUSBASECODE"            688.5 KB    2471 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/STATISTICS/TABLE_STATISTICS
Job "HFY121123"."SYS_IMPORT_TABLE_01" successfully completed at 14:10:47

$ 

这里顺便再提下另外的问题：当然如果我不指定remap_tablespace参数，表导入后的所属表空间也是不会发生变化的，如果当前数据库不存在相应的表空间就会报错了）。

其实，这些都是可举一反三的！

稍稍理解下oracle的想法，理解下这几个参数的含义，其它的备份还原操作自然也就明白知道该怎么使用了！！