前期描述:前面的两个例子都只是将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