有的时候我们不想导出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就可以简单的加上
@来导出远端的数据倒本地。
直接将远端的数据导入到本地。
首先建立到目标端的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/