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

天萃荷净

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实现异机导入导出方法