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

来源：互联网发布：崩坏3矩阵怎么回血编辑：程序博客网时间：2024/05/01 10:10

本次演示的是用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 2015Copyright(c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.Connectedto: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bitProductionWith thePartitioning, Real Application Clusters, Automatic Storage Management, OLAP,DataMining and Real Application Testing optionsStarting"SCOTT"."SYS_EXPORT_TABLE_01":  scott/******** tables=tab_testdirectory=dump_dir network_link=from_110_link dumpfile=exp.dmp logfile=exp.logEstimatein progress using BLOCKS method...Processingobject type TABLE_EXPORT/TABLE/TABLE_DATATotalestimation using BLOCKS method: 64 KBProcessingobject type TABLE_EXPORT/TABLE/TABLE. .exported "SCOTT"."TAB_TEST"                          5.429 KB       1 rowsMastertable "SCOTT"."SYS_EXPORT_TABLE_01" successfullyloaded/unloaded******************************************************************************Dump fileset for SCOTT.SYS_EXPORT_TABLE_01 is:  /u01/app/oracle/dump/exp.dmpJob"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/dumptotal 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 2015Copyright(c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.Connectedto: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bitProductionWith thePartitioning, Real Application Clusters, Automatic Storage Management, OLAP,DataMining and Real Application Testing optionsMastertable "SCOTT"."SYS_IMPORT_FULL_01" successfullyloaded/unloadedStarting"SCOTT"."SYS_IMPORT_FULL_01":  scott/******** directory= dumpfile=Processingobject type TABLE_EXPORT/TABLE/TABLEProcessingobject type TABLE_EXPORT/TABLE/TABLE_DATA. . imported"SCOTT"."TAB_TEST"                          5.429 KB       1 rowsJob"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

0 0