数据泵导入远程oracle,数据泵导入导出远程数据库数据

1、创建数据导出目录

$mkdir -p /home/oralce/dmp_dir

2、创建directory

SYS@PROD1>create directory dmp_dir as '/home/oracle/dmp_dir';

Directory created.

3、创建连接远程数据库的dblink

SYS@PROD1>create public database link emrep connect to system identified by oracle using 'emrep';

Database link created.

4、验证dblink

SYS@PROD1>select * from scott.emp@emrep;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7369 SMITH CLERK 7902 17-DEC-80 800 20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

7566 JONES MANAGER 7839 02-APR-81 2975 20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

7782 CLARK MANAGER 7839 09-JUN-81 2450 10

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

7839 KING PRESIDENT 17-NOV-81 5000 10

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

7876 ADAMS CLERK 7788 23-MAY-87 1100 20

7900 JAMES CLERK 7698 03-DEC-81 950 30

7902 FORD ANALYST 7566 03-DEC-81 3000 20

7934 MILLER CLERK 7782 23-JAN-82 1300 10

14 rows selected.

5、导出远程数据库数据

$ expdp system directory=dmp_dir dumpfile=exp_scott_emp.dmp logfile=exp_scott_emp.log tables=scott.emp network_link=emrep

Export: Release 11.2.0.3.0 - Production on Tue Dec 11 22:30:01 2018

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

Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/******** directory=dmp_dir dumpfile=exp_scott_emp.dmp logfile=exp_scott_emp.log tables=scott.emp network_link=emrep

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 64 KB

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

. . exported "SCOTT"."EMP" 8.562 KB 14 rows

Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:

/home/oracle/dmp_dir/exp_scott_emp.dmp

Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at 22:31:18

6、导入远程数据库数据

$ impdp system directory=dmp_dir tables=scott.emp remap_schema=scott:hr exclude=constraint table_exists_action=append logfile=imp_scott.log network_link=emrep

Import: Release 11.2.0.3.0 - Production on Tue Dec 11 22:42:34 2018

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

Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SYSTEM"."SYS_IMPORT_TABLE_02": system/******** directory=dmp_dir tables=scott.emp remap_schema=scott:hr exclude=constraint table_exists_action=append logfile=imp_scott.log network_link=emrep

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 64 KB

Processing object type TABLE_EXPORT/TABLE/TABLE

. . imported "HR"."EMP" 14 rows

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Job "SYSTEM"."SYS_IMPORT_TABLE_02" successfully completed at 22:42:42