通过EXPDP/IMPDP导出导入远端数据倒本地

有的时候我们不想导出EXPDP文件然后FTP到目标服务器IMPDP 因为这样会占用大量的空间，并且浪费时间，我们就可以通过如下的方式
直接将远端的数据导入到本地。

首先建立到目标端的DBLINK 使用导出用户的用户名


create public database link link33
connect to testimp identified by gelc123123
using '(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.45.33)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = yjfcore)
    )
)';


如果采用SCHEMAS方式导出
这里注意赋予远端用户testimp用户exp_full_database
grant exp_full_database to testimp;
同时赋予目标端用户testimp2用户 imp_full_database
grant imp_full_database to testimp2;

否则导入的时候可能报错
ORA-31631: privileges are required
ORA-39149: cannot link privileged user to non-privileged user



然后可以进行远程导入，我这里也同时完成了导入到不同的用户
impdp testimp2/gelc123  job_name=test123_my  schemas=testimp remap_schema=testimp:testimp2 network_link=link33  TRANSFORM=SEGMENT_ATTRIBUTES:n logfile=bakdir:backlog.log
注意这里并没有 dumpfile=bakdir:test.dmp 很显然这里并没有生成任何DMP文件如此可以节约目标服务器空间

给出日志：
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 15.12 MB
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"TESTIMP2" already exists
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 "TESTIMP2"."TEST2"                          127151 rows
. . imported "TESTIMP2"."TEST1"                             732 rows

当然这种情况下及有FULL权限下也可以单表导入
Starting "TESTIMP2"."TEST123_MY":  testimp2/******** job_name=test123_my tables=testimp.TEST1 remap_schema=testimp:testimp2 network_link=link33 TRANSFORM=SEGMENT_ATTRIBUTES:n logfile=bakdir:backlog.log 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . imported "TESTIMP2"."TEST1"                             732 rows
Job "TESTIMP2"."TEST123_MY" successfully completed at 18:27:27


当然也可以不赋予FULL权限，导出一个用户下面的所有OBJECT即可而不都出USER


使用，注意这里没有  schemas=testimp
[oradba@yjftestdb01 dmp]$ impdp testimp2/gelc123  job_name=test123_my   remap_schema=testimp:testimp2 network_link=link33  TRANSFORM=SEGMENT_ATTRIBUTES:n logfile=bakdir:backlog.log


Import: Release 11.2.0.3.0 - Production on Thu Sep 24 18:16:41 2015


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, OLAP, Data Mining and Real Application Testing options
Starting "TESTIMP2"."TEST123_MY":  testimp2/******** job_name=test123_my remap_schema=testimp:testimp2 network_link=link33 TRANSFORM=SEGMENT_ATTRIBUTES:n logfile=bakdir:backlog.log 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 15.12 MB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . imported "TESTIMP2"."TEST2"                          127151 rows
. . imported "TESTIMP2"."TEST1"                             732 rows
Job "TESTIMP2"."TEST123_MY" successfully completed at 18:17:12

这里导入就没有用户的信息了
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"TESTIMP2" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

然后测试一下远程导入到本地的EXPDP
expdp testimp2/gelc123  job_name=test123_my  schemas=testimp  network_link=link33   logfile=bakdir:backlog.log  dumpfile=bakdir:test.dmp


给出日志：
Total estimation using BLOCKS method: 15.12 MB
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 "TESTIMP"."TEST2"                           12.46 MB  127151 rows
. . exported "TESTIMP"."TEST1"                           88.54 KB     732 rows




这样就可以导出远端的数据，一般来说EXPDP是不能导出远端的数据到本地的但是使用NETWORK_LINK的方式可以实现，但是EXP就可以简单的加上
@来导出远端的数据倒本地。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7728585/viewspace-1808443/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7728585/viewspace-1808443/