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------------------------------OUTLNSYSSYSTEMAPPQOSSYSDBSNMPDIPORACLE_OCM7 rows selected.SYS@PROD3>

-- 查看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              INDEXDEPT                 TABLEEMP                  TABLEPK_EMP               INDEXBONUS                TABLESALGRADE             TABLE6 rows selected.-- 将scott用户unlockSYS@PROD1>alter user scott identified by oracle account unlock;User altered.SYS@PROD1>conn scott/oracleConnected.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.-- 创建directorySYS@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=scottImport: Release 11.2.0.3.0 - Production on Sat Dec 9 15:15:53 2017Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsStarting "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_DATATotal estimation using BLOCKS method: 192 KBProcessing object type SCHEMA_EXPORT/USERProcessing object type SCHEMA_EXPORT/SYSTEM_GRANTProcessing object type SCHEMA_EXPORT/ROLE_GRANTProcessing object type SCHEMA_EXPORT/DEFAULT_ROLEProcessing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing 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 rowsProcessing object type SCHEMA_EXPORT/TABLE/INDEX/INDEXProcessing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINTProcessing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINTProcessing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSJob "SYS"."SYS_IMPORT_SCHEMA_01" successfully completed at 15:16:59[oracle@edbjr2p1 dbs]$ 

-- 简单的验证

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

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

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=2Export: Release 11.2.0.3.0 - Production on Sat Dec 9 15:46:54 2017Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsStarting "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 KBProcessing object type SCHEMA_EXPORT/USERProcessing object type SCHEMA_EXPORT/DEFAULT_ROLEProcessing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAMaster table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded******************************************************************************Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:  /home/oracle/OCM_DIR/expdat.dmpJob "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/oracleConnected.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         2HR@PROD3>

-- 再次用上面的语句导出。看看结果 .说明，使用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=2Export: Release 11.2.0.3.0 - Production on Sat Dec 9 15:55:22 2017Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsStarting "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_DATATotal estimation using BLOCKS method: 64 KBProcessing object type SCHEMA_EXPORT/USERProcessing object type SCHEMA_EXPORT/SYSTEM_GRANTProcessing object type SCHEMA_EXPORT/ROLE_GRANTProcessing object type SCHEMA_EXPORT/DEFAULT_ROLEProcessing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing object type SCHEMA_EXPORT/TABLE/TABLE. . exported "HR"."T"                                    5.007 KB       2 rowsMaster table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded******************************************************************************Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:  /home/oracle/OCM_DIR/expdat.dmpJob "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 15:55:47

++++++++++++++++++++=

end