联系:手机/微信(+86 17813235971) QQ(107644445)
标题:Data pump 中network_link参数的使用
作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
一、准备工作
1、源端
[oracle@ECP-UC-DB1 ~]$ sqlplus chf/xifenfei SQL*Plus: Release 10.2.0.4.0 - Production on Tue Nov 29 15:07:35 2011 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select count(*) from T2_1 ; COUNT(*) ---------- 100000
2、目标端
--tns配置 test = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.12)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = test) ) ) [oracle@node1 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Tue Nov 29 14:57:08 2011 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing options SQL> create public database link dblink_test 2 connect to chf identified by xifenfei using 'test'; Database link created. SQL> grant read,write on directory test_dir to xff; Grant succeeded.
二、导入方式
1、expdp导出,impdp导入
[oracle@node1 ~]$ expdp chf/xff directory=test_dir dumpfile=t1_2.dmp network_link=dblink_test tables=T2_1 Export: Release 11.2.0.3.0 - Production on Tue Nov 29 15:05:36 2011 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing options Starting "CHF"."SYS_EXPORT_TABLE_01": chf/******** directory=test_dir dumpfile=t1_2.dmp network_link=dblink_test tables=T2_1 Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 22 MB Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS . . exported "CHF"."T2_1" 9.326 MB 100000 rows Master table "CHF"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for CHF.SYS_EXPORT_TABLE_01 is: /tmp/t1_2.dmp Job "CHF"."SYS_EXPORT_TABLE_01" successfully completed at 15:06:52 --1、userid使用的是目标端(expdp端)登录 --2、tables对应的表所有者需要和userid相同 [oracle@node1 tmp]$ ll /tmp/t1_2.dmp -rw-r----- 1 oracle oinstall 9859072 11-29 15:06 /tmp/t1_2.dmp [oracle@node1 tmp]$ impdp xff/xifenfei directory=test_dir dumpfile=t1_2.dmp REMAP_SCHEMA=chf:xff REMAP_TABLESPACE=odu:users Import: Release 11.2.0.3.0 - Production on Tue Nov 29 15:30:15 2011 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing options Master table "XFF"."SYS_IMPORT_FULL_01" successfully loaded/unloaded Starting "XFF"."SYS_IMPORT_FULL_01": xff/******** directory=test_dir dumpfile=t1_2.dmp REMAP_SCHEMA=chf:xff REMAP_TABLESPACE=odu:users Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/TABLE_DATA . . imported "XFF"."T2_1" 9.326 MB 100000 rows Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Job "XFF"."SYS_IMPORT_FULL_01" successfully completed at 15:30:18 [oracle@node1 tmp]$ sqlplus xff/xifenfei SQL*Plus: Release 11.2.0.3.0 Production on Tue Nov 29 15:30:43 2011 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing options SQL> select count(*) from t2_1; COUNT(*) ---------- 100000
2、使用impdp直接导入
[oracle@node1 tmp]$ impdp chf/xff directory=test_dir network_link=dblink_test REMAP_SCHEMA=chf:xff REMAP_TABLESPACE=odu:users tables=t2_1 Import: Release 11.2.0.3.0 - Production on Tue Nov 29 15:48:49 2011 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing options Starting "CHF"."SYS_IMPORT_TABLE_01": chf/******** directory=test_dir network_link=dblink_test REMAP_SCHEMA=chf:xff REMAP_TABLESPACE=odu:users tables=t2_1 Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 22 MB Processing object type TABLE_EXPORT/TABLE/TABLE . . imported "XFF"."T2_1" 100000 rows Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Job "CHF"."SYS_IMPORT_TABLE_01" successfully completed at 15:49:00 --1、userid使用的是impdp端登录 --2、tables对应的表所有者需要和userid相同(tables的表所有者是源端,使用REMAP_SCHEMA映射owner) [oracle@node1 tmp]$ sqlplus xff/xifenfei SQL*Plus: Release 11.2.0.3.0 Production on Tue Nov 29 15:51:18 2011 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing options SQL> select count(*) from t2_1; COUNT(*) ---------- 100000
如果登录用户和需要导入的表用户名不同,暂不清楚怎么处理
Data pump 中network_link参数的使用续(登录用户和源端用户不一致处理)
https://www.xifenfei.com/2013.html