Chinaunix首页 | 论坛 | 博客
  • 博客访问: 506412
  • 博文数量: 65
  • 博客积分: 1158
  • 博客等级: 少尉
  • 技术积分: 1261
  • 用 户 组: 普通用户
  • 注册时间: 2012-07-18 22:07
文章分类

全部博文(65)

文章存档

2016年(1)

2014年(2)

2013年(9)

2012年(53)

分类: Oracle

2012-11-07 08:48:54

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












阅读(6504) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~