sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 运维DBA > Oracle expdp impdp >

学习笔记:Oracle dump 数据泵network_link实现异机导入导出方法

时间:2016-05-17 22:31   来源:Oracle研究中心   作者:惜分飞   点击:

天萃荷净

Oracle数据泵impdp/expdp时network_link参数实现异机导入/导入 使用方法汇总

一、Oracle数据库环境准备工作

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.

二、Oracle数据泵impdp导入方式

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

如果登录用户和需要导入的表用户名不同,暂不清楚怎么处理
 

-----------------温馨提示--------------------
操作有风险,动手需谨慎
Oracle研究中心
http://www.oracleplus.net
本文由大师惜分飞原创分享,转载请尽量保留本站网址

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之学习笔记:Oracle dump 数据泵network_link实现异机导入导出方法

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/194.html

Oracle研究中心

关键词:

Oracle dump

数据泵中network_link实现异机导入导出

数据泵中network_link参数的使用方法汇总

oralce expdp impdp network_link使用方法