tables、remap_table、remap_tablespace指定多组对象时,使用逗号分隔
tables指定多个表时,tb1、tb2、tb3。
tables=tb1,tb2,tb3
remap_table转换多个表时,TB1转换TB2、TB3转换TB4。
1.remap_table=tb1:tb2 remap_table=tb3:tb4
2.remap_table=tb1:tb2,tb3:tb4
remap_tablespace转换多个表空间时,TP1转换TP2、TP3转换TP4。
1.remap_tablespace=tp1:tp2 remap_tablespace=tp3:tp4
2.remap_tablespace=tp1:tp2,tp3:tp4
impdp关于tables和remap_tables的一些注意事项
1.expdp时写上tables=(表)表示导出某些表;impdp时不写tables条件表示导入dumpfile中的所有表,impdp时写tables=(表)条件表示只导入指定的表,当然如果tables=(dumpfile中的所有表)也就是导入dumpfile中的所有表;且如果用system执行impdp时则tables=(表)时必须加上schema.表名,否则会默认是为system下面的表,会导致报错
2.remap_table如果在同一个schema下进行,虽然不要加remap_schema,但是remap_table冒号前面的表名一定要带schema信息,冒号后面的表名一定不能带schema信息
3.remap_table如果在不同schema的情况下进行,如user1.TB1转换成user2.TB2,user1.TB3转换成user2.TB4,必须加remap_schema,且remap_table冒号前面的表名一定要带schema信息,冒号后面的表名一定不能带schema信息
有如下两种方式
1.remap_table= user1.TB1:TB2 remap_table= user1.TB3:TB4 remap_schema= user1: user2
2.remap_table= user1.TB1:TB2,user1.TB3:TB4 remap_schema= user1: user2
跨schema只导出导入表并重命名的标准方式如下
expdp时tables写上schema.表名并用逗号隔开
impdp时需要remap_schema, tables可写可不写,remap_table冒号前面的表名加上schema,冒号后面的表名不加schema
expdp system/oracle tables=user01.src_tb01,user01.src_tb02,user01.src_tb03 directory=DMP dumpfile=tb_2013.dbf
impdp system/oracle remap_schema=user01:user02 tables=user01.src_tb01,user01.src_tb02,user01.src_tb03 remap_table=user01.src_tb01:dest_tb01,user01.src_tb02:dest_tb02,user01.src_tb03:dest_tb03 directory=IMP dumpfile=tb_2013.dbf
一个案例
A服务器导出用户u01的一张分区表src_tb01
expdp system/oracle directory=DMP dumpfile=tb01.dmp logfile=src_tb01.log tables=u01.src_tb01
导入到B服务器的u02用户,变成src_tb01(没有加tables=u01.tb01,其实加不加无所谓,反正tb01.dmp就是只有一张表u01.tb01)
impdp system/oracle directory=IMP dumpfile=tb01.dmp logfile=dest_tb02.log remap_schema=u01:u02 remap_tablespace=src_ts01:dest_ts01,src_ts02:dest_ts01,src_ts03:dest_ts01,src_ts04:dest_ts01,src_ts05:dest_ts01,src_ts06:dest_ts01,src_ts:dest_ts01
导入到B服务器的u02用户,表名改为dest_tb01(加了tables=u01.src_tb01,其实加不加无所谓,反正ESB_LOG.dm就是只有一张表u01.src_tb01)
impdp system/oracle directory=IMP dumpfile=tb01.dmp logfile=dest_tb01.log remap_schema=u01:u02 remap_tablespace=src_ts01:dest_ts01,src_ts02:dest_ts01,src_ts03:dest_ts01,src_ts04:dest_ts01,src_ts05:dest_ts01,src_ts06:dest_ts01,src_ts:dest_ts01 remap_table=u01.src_tb01:dest_tb01
导入到A服务器的u01用户,表名改为dest_tb01
impdp system/oracle dumpfile=tb01.dmp directory=IMP logfile=dest_tb01.log transform=oid:n tables=u01.src_tb01 remap_table=u01.src_tb01:dest_tb01 content=all
[oracle@cerpproddb01 script]$ impdp system/oracle dumpfile=tb01.dmp directory=IMP logfile=dest_tb01.log transform=oid:n tables=u01.src_tb01 remap_table=u01.src_tb01:dest_tb01 content=all
Import: Release 11.2.0.4.0 - Production on Wed Nov 21 17:22:40 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TABLE_01": system/******** dumpfile=ora-cerpprddb_inca_20181119.dmp directory=MY_DIR logfile=ora-cerpprddb_inca_20181121.log transform=oid:n tables=cerpzs.NP_OP_AP remap_table=cerpzs.NP_OP_AP:NP_OP_AP_20181121 content=all
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "U01"."DEST_TB01" 9.531 MB 256509 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
ORA-31684: Object type INDEX:"U01"."DEST_TB01_PK" already exists
ORA-31684: Object type INDEX:"U01"."IDX_DEST_TB01_ID" already exists
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
ORA-31684: Object type CONSTRAINT:"U01"."DEST_TB01_PK" already exists
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
ORA-39111: Dependent object type INDEX_STATISTICS skipped, base object type INDEX:"U01"."IDX_DEST_TB01_ID" already exists
ORA-39111: Dependent object type INDEX_STATISTICS skipped, base object type INDEX:"U01"."DEST_TB01_PK" already exists
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
ORA-31684: Object type REF_CONSTRAINT:"U01"."DEST_TB01_ID_FK" already exists
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_TABLE_01" completed with 6 error(s) at Wed Nov 21 17:23:13 2018 elapsed 0 00:00:20