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

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

 

转载于:https://www.cnblogs.com/orcl-2018/p/10105952.html