1. 首页
  2. IT资讯

通过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://www.cxybcw.com/183408.html

联系我们

13687733322

在线咨询:点击这里给我发消息

邮件:1877088071@qq.com

工作时间:周一至周五,9:30-18:30,节假日休息

QR code