oracle 10g 使用expdp network_link导出远程数据库到本地文件

远程：

          IP:192.168.10.55,OS:RHEL4.8 64bit,Oracle:10.2.0.5 64bit

          被导出用户:monitor

本地：

       ip: 172.*，WINDOWS XP,oracle 10.2.0.4。

      本地tns :cheniwo

      本地配置的联系到远程的tns:lobom55

1)确定本地可访问远程

F:\oracle\product\10.2.0\db_2\network\admin\sqlnet.ora

已使用 TNSNAMES 适配器来解析别名

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP

(HOST = 192.168.10.55)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = lobomb)))

OK (40 毫秒)

2）在本地建立dblink到远程

F:\Documents and Settings\Administrator>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on 星期四 9月 6 14:25:44 2012

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

连接到:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create database link dl_monitor connect to monitor identified by monitor us

ing 'lobom55';

数据库链接已创建。

SQL> select sysdate from dual@dl_monitor;

SYSDATE

--------------

06-9月 -12

3）数据泵导出

F:\Documents and Settings\Administrator>expdp network_link=dl_monitor dumpfile=n

etwork_monitor.dmp

/*--------------------------------------------------

说明：network_link是你建立的到远程的database link

下边输入的用户名是你本地数据库的认证

dumpfile 将放在data_pump_dir目录定义的本地目录下

-------------------------------*/

Export: Release 10.2.0.4.0 - Production on 星期四, 06 9月, 2012 14:40:17

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

用户名: / as sysdba

连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

启动 "SYS"."SYS_EXPORT_SCHEMA_01":  /******** AS SYSDBA network_link=dl_monitor

dumpfile=network_monitor.dmp

正在使用 BLOCKS 方法进行估计...

处理对象类型 SCHEMA_EXPORT/TABLE/TABLE_DATA

使用 BLOCKS 方法的总估计: 81.68 MB

处理对象类型 SCHEMA_EXPORT/USER

处理对象类型 SCHEMA_EXPORT/SYSTEM_GRANT

处理对象类型 SCHEMA_EXPORT/ROLE_GRANT

处理对象类型 SCHEMA_EXPORT/DEFAULT_ROLE

处理对象类型 SCHEMA_EXPORT/TABLESPACE_QUOTA

处理对象类型 SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

处理对象类型 SCHEMA_EXPORT/SEQUENCE/SEQUENCE

处理对象类型 SCHEMA_EXPORT/TABLE/TABLE

处理对象类型 SCHEMA_EXPORT/TABLE/INDEX/INDEX

处理对象类型 SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

处理对象类型 SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

处理对象类型 SCHEMA_EXPORT/TABLE/COMMENT

处理对象类型 SCHEMA_EXPORT/FUNCTION/FUNCTION

处理对象类型 SCHEMA_EXPORT/PROCEDURE/PROCEDURE

处理对象类型 SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION

处理对象类型 SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE

处理对象类型 SCHEMA_EXPORT/VIEW/VIEW

处理对象类型 SCHEMA_EXPORT/VIEW/COMMENT

处理对象类型 SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

处理对象类型 SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX

处理对象类型 SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_ST

ATISTICS

已成功加载/卸载了主表 "SYS"."SYS_EXPORT_SCHEMA_01"

******************************************************************************

SYS.SYS_EXPORT_SCHEMA_01 的转储文件集为:

  F:\ORACLE\PRODUCT\10.2.0\ADMIN\CHENIWO\DPDUMP\NETWORK_MONITOR.DMP

作业 "SYS"."SYS_EXPORT_SCHEMA_01" 已于 14:51:41 成功完成

4）检查结果：

SQL> set head off

SQL> select * from dba_directories where directory_name='DATA_PUMP_DIR';

SYS

DATA_PUMP_DIR

F:\oracle\product\10.2.0\admin\cheniwo\dpdump\

SQL>

F:\Documents and Settings\Administrator>cd F:\oracle\product\10.2.0\admin\cheniw

o\dpdump

F:\oracle\product\10.2.0\admin\cheniwo\dpdump>dir/w

 驱动器 F 中的卷是 新加卷

 卷的序列号是 88F0-A421

 F:\oracle\product\10.2.0\admin\cheniwo\dpdump 的目录

[.]                   [..]                  cheniwo.sql

CHENIWO_DP.DMP        dp.log                EXPDAT.DMP

export.log            FULL_CHENIWO.DMP      import.log

NETWORK_MONITOR.DMP   TABLES_CHENIWO.DMP    TABLES_CHENIWO2.DMP

TS_CHENIWO.DMP        USER_CHENIWO.DMP

              12 个文件    128,977,860 字节

               2 个目录 155,032,698,880 可用字节

 

F:\oracle\product\10.2.0\admin\cheniwo\dpdump>

expdp中使用连接字符串和network_link的区别

expdp属于服务端工具，而exp属于客户端工具，expdp生成的文件默认是存放在服务端的，而exp生成的文件是存放在客户端的

1.expdp username/password@connect_string   //对于使用这种格式来说，directory使用源数据库创建的，生成的文件存放在服务端。

如何将生成的文件放在目标数据库而不放在源数据库呢，在expdp中使用network_link
2.
a. 创建到服务端的dblink

conn aa/aacc
create database link link_name connect to  username identified by password using 'connect_string' ;//username和password是server端的
b.

conn / as sysdba
create or replace directory dir as 'directory';
grant read,write on directory dir to username;

c.expdp username2/password2  directory=dir network_link=link_name ...  //这里的username2用创建dblink的那个用户aa，directory也是目标数据库创建的

 

比如在本机expdp远程服务器的数据库，先在本机创建到服务端的dblink，然后创建directory及授权，然后expdp useranme2/password2 .....

3.如果想不生成dmp文件而直接导入一个数据库，原理和2类似，直接使用impdp带network_link ，这样可以直接impdp，而绕过了expdp的步骤

下面是network_link常用的三个环境测试

1，服务器端的数据导出到指定的客户端

2，不同数据库间迁移数据。

3，同一个数据库中不同用户之间迁移数据。

一：服务器端的数据导出到指定的客户端

1，修改客户端的TNSNAMES文件 
POWER1 = 
  (DESCRIPTION = 
    (ADDRESS_LIST = 
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.111.13)(PORT = 1521)) 
    ) 
    (CONNECT_DATA = 
      (SERVER = DEDICATED) 
      (SERVICE_NAME = power1) 
    ) 
  ) 
2，创建dblink 
SQL> CREATE PUBLIC DATABASE LINK "POWER1" 
  2  CONNECT TO scott 
  3  IDENTIFIED BY "Oracle" 
  4  USING 'POWER1'; 
 
Database link created. 
 
SQL> select * from dual@power1; 
 
D 
- 
X 
3，directory目录 
SQL> set lines 170 
SQL> col owner for a15 
SQL> col directory_name for a60 
SQL>  col directory_name for a30 
SQL> col DIRECTORY_PATH for a70 
SQL> select * from dba_directories; 
 
OWNER          DIRECTORY_NAME                DIRECTORY_PATH 
--------------- ------------------------------ ---------------------------------------------------------------------- 
SYS            DUMP                          /tmp 
SYS            TOAD_BDUMP_DIR                /u01/app/oracle/diag/rdbms/orcl11g/orcl11g/trace 
SYS            XMLDIR                        /u01/app/oracle/product/11.2/db_1/rdbms/xml 
SYS            DATA_PUMP_DIR                  /u01/app/oracle/admin/orcl11g/dpdump/ 
SYS            ORACLE_OCM_CONFIG_DIR          /u01/app/oracle/product/11.2/db_1/ccr/state 
如果不存在使用create directory创建再用grant授予用户权限 
4，导数据 
[oracle11g@rhel4 admin]$ expdp system/oracle directory=dump dumpfile=scott_test.dmp logfile=scott_log.dmp  network_link='power1' schemas=scott 
 
Export: Release 11.2.0.3.0 - Production on Tue Mar 26 18:22:28 2013 
 
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 
ORA-31631: privileges are required 
ORA-39149: cannot link privileged user to non-privileged user 
 
 
grant这个命令要在源端数据库上面执行 
SQL> GRANT exp_full_database TO scott;  
 
Grant succeeded. 
回到客户端上面 
[oracle11g@rhel4 admin]$ expdp system/oracle directory=dump dumpfile=scott_test.dmp logfile=scott_log.dmp  network_link='power1' schemas=scott 
 
Export: Release 11.2.0.3.0 - Production on Tue Mar 26 19:40:03 2013 
 
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 
ORA-39001: invalid argument value 
ORA-39000: bad dump file specification 
ORA-31641: unable to create dump file "/tmp/scott_test.dmp" 
ORA-27038: created file already exists 
Additional information: 1 
 
 
[oracle11g@rhel4 admin]$ expdp system/oracle directory=dump dumpfile=scott_test.dmp logfile=scott_log.dmp  network_link='power1' schemas=scott REUSE_DUMPFILES=Y 
 
Export: Release 11.2.0.3.0 - Production on Tue Mar 26 19:42:29 2013 
 
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 "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** directory=dump dumpfile=scott_test.dmp logfile=scott_log.dmp network_link=power1 schemas=scott REUSE_DUMPFILES=Y  
Estimate in progress using BLOCKS method... 
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA 
Total estimation using BLOCKS method: 2.129 GB 
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 
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE 
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE 
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX 
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT 
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS 
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT 
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS 
. . exported "SCOTT"."TEST"                              1.800 GB 19096576 rows 
. . exported "SCOTT"."DEPT"                              5.929 KB      4 rows 
. . exported "SCOTT"."EMP"                              8.562 KB      14 rows 
. . exported "SCOTT"."SALGRADE"                          5.859 KB      5 rows 
. . exported "SCOTT"."BONUS"                                0 KB      0 rows 
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded 
****************************************************************************** 
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is: 
  /tmp/scott_test.dmp 
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 19:46:1  
已经成功导出到客户端指定的位置