通過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




然後測試一下遠端匯入到本地的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/，如需轉載，請註明出處，否則將追究法律責任。