oracle data pump导出,Data Pump执行导出数据到目标数据库

前期描述：前面的两个例子都只是将dmp文件导出到了数据库服务器所在的目录，但是我们大多数操作是需要将dmp文件导出到目标机器上的(通常我们会在目标机器上执行exp,expdp命令)，如果要在目标机器上执行expdp命令的话，那么需要在目标机器上配置一个database link连接到源数据库,同时在目标数据库中创建一个directory目录，并将这个目录的读写权限赋给要执行expdp命令的用户;下面通过一个例子说明

实验环境：源数据服务器是一台linux服务器(192.168.39.103),目标数据库也是一台linux服务器(192.168.39.232)

1.首先在目标数据库上建立到源数据库的dblink

既然要建立到源数据库的dblink，那么至少得在目标数据库的tnsname.ora上配置到源数据库的连接配置参数，这步我就不说了，随便拷贝一下配置，改改参数就是了。

2.接着在目标数据库上建立dblink和directory对象

--建立database link(在232上目标机器)

SYS@oracle10>conn scott/tigerConnected.SCOTT@oracle10>create database link test_from_103 connect to gdyxhd identified by gdyxhd using 'ORA10';

SCOTT@oracle10>conn / as sysdba

Connected.

SYS@oracle10>drop directory dump_dir;

Directory dropped.

SYS@oracle10>create or replace directory dump_dir as '/u01/oradata/backup/dmp';

这里我是用sysdba也就是sys用户创建的directory

Directory created.

SYS@oracle10>grant read,write on directory dump_dir to scott; #在sys下将directory的read，write权限授予scott，这个很关键

Grant succeeded.

--导出192.168.39.103(源数据库的)gdyxhd下的T_BSS_CUST_INFO表    #方法一使用等号

[oracle@localhost dmp]$ expdp scott/tiger directory= dump_dir dumpfile=t_bss_cust_info.dmp network_link=test_from_103  include=table:\" = \(\'T_BSS_CUST_INFO\'\)\";

Export: Release 10.2.0.4.0 - Production on Tuesday, 06 November, 2012 21:34:50

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production

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

Starting "SCOTT"."SYS_EXPORT_SCHEMA_02":  scott/******** directory= dump_dir dumpfile=t_bss_cust_info.dmp network_link=test_from_103 include=table:" = ('T_BSS_CUST_INFO')"

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 704 KB

Processing object type SCHEMA_EXPORT/TABLE/TABLE

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/COMMENT

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

. . exported "GDYXHD"."T_BSS_CUST_INFO"                  563.3 KB    8454 rows

Master table "SCOTT"."SYS_EXPORT_SCHEMA_02" successfully loaded/unloaded

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

Dump file set for SCOTT.SYS_EXPORT_SCHEMA_02 is:

/u01/oradata/backup/dmp/t_bss_cust_info.dmp

Job "SCOTT"."SYS_EXPORT_SCHEMA_02" successfully completed at 21:35:03

--方法2 使用in关键字

[oracle@localhost dmp]$ expdp scott/tiger directory= dump_dir dumpfile=t_bss_cust_info.dmp network_link=test_from_103  include=table:\" in \(\'T_BSS_CUST_INFO\'\)\";

Export: Release 10.2.0.4.0 - Production on Tuesday, 06 November, 2012 21:34:07

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production

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

Starting "SCOTT"."SYS_EXPORT_SCHEMA_02":  scott/******** directory= dump_dir dumpfile=t_bss_cust_info.dmp network_link=test_from_103 include=table:" in ('T_BSS_CUST_INFO')"

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 704 KB

Processing object type SCHEMA_EXPORT/TABLE/TABLE

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/COMMENT

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

. . exported "GDYXHD"."T_BSS_CUST_INFO"                  563.3 KB    8454 rows

Master table "SCOTT"."SYS_EXPORT_SCHEMA_02" successfully loaded/unloaded

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

Dump file set for SCOTT.SYS_EXPORT_SCHEMA_02 is:

/u01/oradata/backup/dmp/t_bss_cust_info.dmp

Job "SCOTT"."SYS_EXPORT_SCHEMA_02" successfully completed at 21:34:18

--方法3 使用like

[oracle@localhost dmp]$ expdp scott/tiger directory= dump_dir dumpfile=t_bss_cust_info.dmp network_link=test_from_103  include=table:\"like \'T_BSS_CUST_INFO\'\";

Export: Release 10.2.0.4.0 - Production on Tuesday, 06 November, 2012 21:32:52

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production

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

Starting "SCOTT"."SYS_EXPORT_SCHEMA_02":  scott/******** directory= dump_dir dumpfile=t_bss_cust_info.dmp network_link=test_from_103 include=table:"like 'T_BSS_CUST_INFO'"

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 704 KB

Processing object type SCHEMA_EXPORT/TABLE/TABLE

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/COMMENT

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

. . exported "GDYXHD"."T_BSS_CUST_INFO"                  563.3 KB    8454 rows

Master table "SCOTT"."SYS_EXPORT_SCHEMA_02" successfully loaded/unloaded

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

Dump file set for SCOTT.SYS_EXPORT_SCHEMA_02 is:

/u01/oradata/backup/dmp/t_bss_cust_info.dmp

Job "SCOTT"."SYS_EXPORT_SCHEMA_02" successfully completed at 21:33:03

--当然最常用的最合理的应该是用parfile咯，这里不多说了

总结下上面导出时我遇上的一些问题，首先就是我是用sysdba创建的directory，然后将这个directory的read，write权限赋予我创建database link的的那个用户，这里我是用的scott，其实用谁无所谓，只要是这个用户创建了directory对象；还有就是network_link=test_from_103 指定的参数，这里的参数值只要就是我刚才创建的database link时的linkname，我这里是用的test_from_103 ，导出这个的用户用的正是创建这个database link所用到的scott用户。这里并不需要scott有什么dba权限，只要把在sys下建立的directory的read、write权限赋予创建database link的用户(这里是scott)就行了。

实际就是通过创建database link的用户执行导出命令，通过database link在源数据库通过database link指定的用户导出的，这里执行expdp命令的scott其实只是一个database link的载体。

[oracle@localhost dmp]$ pwd

/u01/oradata/backup/dmp

[oracle@localhost dmp]$ ls -la

total 704

drwxr-xr-x 2 oracle oinstall   4096 Nov  6 21:53 .

drwxr-xr-x 3 oracle oinstall   4096 Nov  6 15:48 ..

-rw-r--r-- 1 oracle oinstall   1362 Nov  6 21:53 export.log

-rw-r----- 1 oracle oinstall 704512 Nov  6 21:53 t_bss_cust_info.dmp