在使用 IMPDP/EXPDP 时通过 network_link 进行远程导出导入

通过 impdp / expdp 的network_link 选项，可以通过远程进行数据泵的调用。

1 IMPDP+network_link, 直接导入到目标库中（从dblink对应的远端库）

2 EXPDP+network_link,直接将远端库上的数据，导出到本地机器上

1 PROD3库是本地库，PROD1库是远端库，在PROD3上建立到PROD1的dblink，通过impdp+dblink，将PROD1库上的数据直接imp到PROD3库上。过程如下：

– 在PROD3上查询，没有scott用户

SYS@PROD3>select username from dba_users;
 
USERNAME
------------------------------
OUTLN
SYS
SYSTEM
APPQOSSYS
DBSNMP
DIP
ORACLE_OCM
 
7 rows selected.
复制

– 查看scott用户的对象的大小

SYS@PROD1>select sum(bytes/1024/1024) from dba_segments where owner='SCOTT';
 
SUM(BYTES/1024/1024)
--------------------
               .3125
复制

–查看scott的对象

SYS@PROD1>select object_name,object_type from dba_objects where owner='SCOTT';
 
OBJECT_NAME          OBJECT_TYPE
-------------------- --------------------
PK_DEPT              INDEX
DEPT                 TABLE
EMP                  TABLE
PK_EMP               INDEX
BONUS                TABLE
SALGRADE             TABLE
 
6 rows selected.
复制

– 将scott用户unlock

SYS@PROD1>alter user scott identified by oracle account unlock;
 
User altered.
 
SYS@PROD1>conn scott/oracle
Connected.
SCOTT@PROD1>
复制

–在PROD3上导入，
–创建database link .到PROD1

SYS@PROD3>create public database link to_prod1 connect to system identified by oracle using 'PROD1';
 
Database link created.
复制

– 创建directory

SYS@PROD3>create directory OCM_DIR as '/home/oracle/OCM_DIR';
 
Directory created.
复制

– 导入

[oracle@edbjr2p1 dbs]$ export ORACLE_SID=PROD3
[oracle@edbjr2p1 dbs]$ impdp \'/ as sysdba \' directory=OCM_DIR logfile=imp_to_PROD3.log network_link=to_prod1 schemas=scott
 
Import: Release 11.2.0.3.0 - Production on Sat Dec 9 15:15:53 2017
 
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 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_IMPORT_SCHEMA_01":  "/******** AS SYSDBA" directory=OCM_DIR logfile=imp_to_PROD3.log network_link=to_prod1 schemas=scott 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . imported "SCOTT"."DEPT"                                   4 rows
. . imported "SCOTT"."EMP"                                   14 rows
. . imported "SCOTT"."SALGRADE"                               5 rows
. . imported "SCOTT"."BONUS"                                  0 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYS"."SYS_IMPORT_SCHEMA_01" successfully completed at 15:16:59
 
复制

– 简单的验证

SYS@PROD3>select index_name from dba_indexes where owner='SCOTT';
 
INDEX_NAME
------------------------------
PK_DEPT
PK_EMP
 
SYS@PROD3>select count(*) from scott.dept 
  2  union all
  3  select count(*) from scott.dept@to_prod1;
 
  COUNT(*)
----------
         4
         4
 
复制

2 PROD2 是本地库，PROD3是远程库，在PROD2上使用expdp+network_Link参数，会导出文件，导出的文件是PROD3上的数据对应的文件。也即是说，远程expdp文件到本地了。

– 在PROD2上创建到PROD3 的dblink to_PROD3

create public database link to_prod3 connect to system identified by oracle using 'PROD3'
 
SYS@PROD2>create public database link to_prod3 connect to system identified by oracle using 'PROD3'
  2  ;
 
Database link created.
复制

– 导出 好像没有导入到PROD3上，直接到本地了。

/home/oracle/OCM_DIR/expdat.dmp
expdp \'/ as sysdba \' directory=OCM_DIR logfile=exp_from_prod2.log network_link=to_prod3 schemas=SH,OE,HR parallel=2
 
[oracle@edbjr2p1 dbs]$ expdp \'/ as sysdba \' directory=OCM_DIR logfile=exp_from_prod2.log network_link=to_prod3 schemas=SH,OE,HR parallel=2
 
Export: Release 11.2.0.3.0 - Production on Sat Dec 9 15:46:54 2017
 
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 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01":  "/******** AS SYSDBA" directory=OCM_DIR logfile=exp_from_prod2.log network_link=to_prod3 schemas=SH,OE,HR parallel=2 
Estimate in progress using BLOCKS method...
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
  /home/oracle/OCM_DIR/expdat.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 15:47:14
复制

– 在HR上创建个表

SYS@PROD3>grant connect,resource,create table to hr;
 
Grant succeeded.
 
SYS@PROD3>conn hr/oracle
Connected.
HR@PROD3>create table t (id number);
 
Table created.
 
HR@PROD3>insert into t values(1);
 
1 row created.
 
HR@PROD3>insert into t values(2);
 
1 row created.
 
HR@PROD3>commit;
 
Commit complete.
 
HR@PROD3>select * from t;
 
        ID
----------
         1
         2
 
复制

– 再次用上面的语句导出。看看结果 .说明，使用networklink是，把远程数据库上的数据，expdp到本地。

expdp \'/ as sysdba \' directory=OCM_DIR logfile=exp_from_prod2_1.log network_link=to_prod3 schemas=SH,OE,HR parallel=2
[oracle@edbjr2p1 dbs]$ expdp \'/ as sysdba \' directory=OCM_DIR logfile=exp_from_prod2_1.log network_link=to_prod3 schemas=SH,OE,HR parallel=2
 
Export: Release 11.2.0.3.0 - Production on Sat Dec 9 15:55:22 2017
 
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 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01":  "/******** AS SYSDBA" directory=OCM_DIR logfile=exp_from_prod2_1.log network_link=to_prod3 schemas=SH,OE,HR parallel=2 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . exported "HR"."T"                                    5.007 KB       2 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
  /home/oracle/OCM_DIR/expdat.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 15:55:47

复制