翔之天空的专栏

坚持数据库革命道路一直走下去

用dblink从源数据库导出expdp文件到目标数据库


本次演示的是用dblink,直接把expdp文件从源数据库 直接传送到 目标服务器中


源数据库ip:192.168.103.110

目标数据库ip:192.168.103.106

 

 

源数据库:

创建测试表,用于导出测试:

SQL>conn scott/tiger;
Connected.

SQL>create table tab_test(a int);
Tablecreated.

SQL>insert into tab_test select 1 from dual;
1 row created.

SQL>commit;
Commitcomplete.

SQL>select * from tab_test;

   A
----------
   1

 


目标数据库:

[oracle@rac1dump]$ sqlplus / as sysdba

授予scott创建dblink的权限

SQL>grant create public database link to scott;

Grantsucceeded.

授予scott创建目录的权限

SQL>grant create any directory to scott;

Grantsucceeded.

切换到scott用户

SQL>conn scott/tiger;

Connected.

创建dblink

SQL>create public database link from_110_link connect to scott  identified by tiger
  2 using '(DESCRIPTION = 
  3           (ADDRESS_LIST = 
  4                  (ADDRESS = (PROTOCOL =TCP)(HOST = 192.168.103.110)(PORT =1521)) 
  5           ) 
  6           (CONNECT_DATA =  (SERVICE_NAME =orcl) 
  7           ) 
  8        )';

Databaselink created.


创建接受数据文件的目录

SQL>create directory dump_dir as '/u01/app/oracle/dump';

Directory created.

用oracle用户导出expdp文件

[oracle@rac1~]$ expdp scott/tiger tables=tab_test directory=dump_dir network_link=from_110_link dumpfile=exp.dmp logfile=exp.log

 

Export:Release 11.2.0.4.0 - Production on Sat Nov 21 15:08:30 2015

Copyright(c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connectedto: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bitProduction
With thePartitioning, Real Application Clusters, Automatic Storage Management, OLAP,
DataMining and Real Application Testing options
Starting"SCOTT"."SYS_EXPORT_TABLE_01":  scott/******** tables=tab_testdirectory=dump_dir network_link=from_110_link dumpfile=exp.dmp logfile=exp.log
Estimatein progress using BLOCKS method...
Processingobject type TABLE_EXPORT/TABLE/TABLE_DATA
Totalestimation using BLOCKS method: 64 KB
Processingobject type TABLE_EXPORT/TABLE/TABLE
. .exported "SCOTT"."TAB_TEST"                          5.429 KB       1 rows
Mastertable "SCOTT"."SYS_EXPORT_TABLE_01" successfullyloaded/unloaded
******************************************************************************
Dump fileset for SCOTT.SYS_EXPORT_TABLE_01 is:
  /u01/app/oracle/dump/exp.dmp
Job"SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at SatNov 21 15:08:38 2015 elapsed 0 00:00:07

查看刚刚导出的文件(dump_dir目录):

[oracle@rac1~]$ ll /u01/app/oracle/dump

total 96
-rw-r-----1 oracle dba 94208 Nov 21 15:08 exp.dmp
-rw-r--r--1 oracle dba  1137 Nov 21 15:08 exp.log


再把文件导入到数据库中:

[oracle@rac1~]$ impdp scott/tiger  DIRECTORY=dump_dir   dumpfile= exp.dmp

 
Import:Release 11.2.0.4.0 - Production on Sat Nov 21 15:12:51 2015

Copyright(c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connectedto: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bitProduction
With thePartitioning, Real Application Clusters, Automatic Storage Management, OLAP,
DataMining and Real Application Testing options
Mastertable "SCOTT"."SYS_IMPORT_FULL_01" successfullyloaded/unloaded
Starting"SCOTT"."SYS_IMPORT_FULL_01":  scott/******** directory= dumpfile=
Processingobject type TABLE_EXPORT/TABLE/TABLE
Processingobject type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported"SCOTT"."TAB_TEST"                          5.429 KB       1 rows
Job"SCOTT"."SYS_IMPORT_FULL_01" successfully completed at SatNov 21 15:12:58 2015 elapsed 0 00:00:05

查询,有表和数据

SQL> select * from tab_test;

   A
----------
   1

 

 

阅读更多
版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/fly43108622/article/details/49965245
文章标签: dblink expdp
个人分类: Oracle 数据迁移
上一篇Linux防火墙开放端口
下一篇oracle的用户session
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭