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

全部博文(65)

文章存档

2016年(1)

2014年(2)

2013年(9)

2012年(53)

分类: Oracle

2012-11-06 11:42:17

1.因为要为导出的dump文件制定Directory对象,因此首先需要创建相关的Directory对象并授予用户相应的读写权限
SQL>create directory dump_file_dir as  '/data/backup/dmp';   --需要DBA权限或者create any directory权限
赋予用户对directory对象的读写权限
SQL>grant read,write on directory dump_file_dir to scott;

上面就是设置了data dump操作的文件均存储在dump_file_dir对象对应的路径中,改对象实际的操作系统地址为/data/backup/dmp,如果要有对该目录对象对应的操作系统目录有读写的权限的话,要对用户进行目录对象的授权读写操作。

下面是一个实际的例子:
[oracle@api ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Nov 6 11:16:48 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> conn / as sysdba
Connected.
SQL> create directory dump_file_dir as '/u01/app/oracle/backup/dmp';

Directory created.

SQL> grant read,write on directory dump_file_dir to gdyxhd;

Grant succeeded.

注意:上面那个制定的操作系统的目录必须是开始存在的,做了实验,如果开始不创建这个系统路径,目录还是能够创建成功,但是当用这个目录去做data dump时指定的目录的时候,会报错,下面会举例子。

--先看看我刚才指定的那个操作系统目录下面有没有backup这个目录
[oracle@api ~]$ cd /u01/app/oracle/
[oracle@api oracle]$ ls -la
???56
drwxrwxr-x 7 oracle oinstall 4096 2011-04-14 .
drwxrwxr-x 3 oracle oinstall 4096 2011-04-14 ..
drwxr-x--- 3 oracle oinstall 4096 2011-04-14 admin
drwxr-x--- 3 oracle oinstall 4096 2011-04-14 flash_recovery_area
drwxr-x--- 3 oracle oinstall 4096 2011-04-14 oradata
drwxrwx--- 6 oracle oinstall 4096 2011-04-14 oraInventory
drwxrwx--- 3 oracle oinstall 4096 2011-04-14 product

--开始在/u01/app/oracle/是没有backup目录的,但是我之前还是创建目录成功了,显然对于Oracle当我们创建目录的时候,指定的操作系统不存在的时候,创建directory的时候,不会去检查操作系统存不存在,也不会主动去为我们创建。

--前面知道没有创建,那么我现在试着去data dump一下试试,看Oracle会不会主动帮我们创建
[oracle@api oracle]$ expdp gdyxhd/gdyxhd directory= dump_file_dir dumpfile=t_bss_cust_info.dmp nologfile=y tables=t_bss_cust_info query=t_bss_cust_info:\"where user_code = \'JYPN0037\'\"

Export: Release 10.2.0.1.0 - Production on Tuesday, 06 November, 2012 11:27:21

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31641: unable to create dump file "/u01/app/oracle/backup/dmp/t_bss_cust_info.dmp"
ORA-27040: file create error, unable to create file
Linux Error: 2: No such file or directory

--显然Oracle本身并没有帮我创建这个目录,没办法,只能自己创建咯
[oracle@api ~]$ cd /u01/app/oracle/
[oracle@api oracle]$ ls -la
???56
drwxrwxr-x 7 oracle oinstall 4096 2011-04-14 .
drwxrwxr-x 3 oracle oinstall 4096 2011-04-14 ..
drwxr-x--- 3 oracle oinstall 4096 2011-04-14 admin
drwxr-x--- 3 oracle oinstall 4096 2011-04-14 flash_recovery_area
drwxr-x--- 3 oracle oinstall 4096 2011-04-14 oradata
drwxrwx--- 6 oracle oinstall 4096 2011-04-14 oraInventory
drwxrwx--- 3 oracle oinstall 4096 2011-04-14 product
[oracle@api oracle]$ mkdir backup
[oracle@api oracle]$ cd backup
[oracle@api backup]$ mkdir dmp

--现在好了,操作系统路径也创建起来了,再用data dump试试
[oracle@api oracle]$ expdp gdyxhd/gdyxhd directory= dump_file_dir dumpfile=t_bss_cust_info.dmp nologfile=y tables=t_bss_cust_info query=t_bss_cust_info:\"where user_code = \'JYPN0037\'\"

Export: Release 10.2.0.1.0 - Production on Tuesday, 06 November, 2012 11:28:19

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "GDYXHD"."SYS_EXPORT_TABLE_01":  gdyxhd/******** directory= dump_file_dir dumpfile=t_bss_cust_info.dmp nologfile=y tables=t_bss_cust_info query=t_bss_cust_info:"where user_code = 'JYPN0037'"
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 704 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "GDYXHD"."T_BSS_CUST_INFO"                  8.359 KB       1 rows
Master table "GDYXHD"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for GDYXHD.SYS_EXPORT_TABLE_01 is:
  /u01/app/oracle/backup/dmp/t_bss_cust_info.dmp
Job "GDYXHD"."SYS_EXPORT_TABLE_01" successfully completed at 11:29:22

--很显然的,得到了我们想要的结果

对于这条data dump命令还说明一下:
directory就是我们刚才创建的那个directory对象
dumpfile就是我们制定的在操作系统路径下创建的文件名
nologfile=y不产生日志文件,
tables指定我们要导出的表
query制定我们导出表时记录要满足的条件,因为这里要用到的条件字段是varchar2类型,所以不能用常规的'(单引号)作为分隔符这里我们用的"(双引号)转义一下就能得到我们要的结果。

看看操作系统目录下有没有我刚才data dump出来的文件
[oracle@api dmp]$ pwd
/u01/app/oracle/backup/dmp
[oracle@api dmp]$ ls -la
???148
drwxr-xr-x 2 oracle oinstall   4096 11-06 11:28 .
drwxr-xr-x 3 oracle oinstall   4096 11-06 11:28 ..
-rw-r----- 1 oracle oinstall 139264 11-06 11:29 t_bss_cust_info.dmp


--再来看看更加高级的一些用法

--parfile
上面那个例子首先要调整的就是expdp的时候,后面的参数太多了,可能会超过操作系统最大的字符允许数(操作系统对命令的最大字符数是有限制的)
所以指定parfile这个参数就很有必要了,看下面的例子

我首先在dmp目录下面建立一个参数文件名字为t_bss_cust_info.par,加入以下内容:
directory=dump_file_dir
dumpfile=t_bss_cust_info_par.dmp
logfile=t_bss_cust_info_par.log
include=table:"like 't_bss_cust_in%'"
query=t_bss_cust_info:"where user_code = 'JYPN0037'"

[oracle@api dmp]$ cat t_bss_cust_info.par
directory=dump_file_dir
dumpfile=t_bss_cust_info_par.dmp
logfile=t_bss_cust_info_par.log
include=table:"like 't_bss_cust_in%'"
query=t_bss_cust_info:"where user_code = 'JYPN0037'"

然后执行data dump
[oracle@api dmp]$ expdp gdyxhd/gdyxhd parfile=t_bss_cust_info.par

Export: Release 10.2.0.1.0 - Production on Tuesday, 06 November, 2012 15:03:08

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "GDYXHD"."SYS_EXPORT_SCHEMA_02":  gdyxhd/******** parfile=t_bss_cust_info.par
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
ORA-39168: Object path TABLE was not found.
ORA-31655: no data or metadata objects selected for job
Job "GDYXHD"."SYS_EXPORT_SCHEMA_02" completed with 2 error(s) at 15:03:50
报错了,找不到这个表,将其换成大写试试。

[oracle@api dmp]$ cat t_bss_cust_info.par
directory=dump_file_dir
dumpfile=t_bss_cust_info_par.dmp
logfile=t_bss_cust_info_par.log
include=table:"like 'T_BSS_CUST_%'"
query=t_bss_cust_info:"where user_code = 'JYPN0037'"
[oracle@api dmp]$ expdp gdyxhd/gdyxhd parfile=t_bss_cust_info.par

Export: Release 10.2.0.1.0 - Production on Tuesday, 06 November, 2012 15:10:13

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "GDYXHD"."SYS_EXPORT_SCHEMA_02":  gdyxhd/******** parfile=t_bss_cust_info.par
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1.5 MB
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"                  8.359 KB       1 rows
. . exported "GDYXHD"."T_BSS_CUST_INFO_BAK"              485.3 KB    8313 rows
. . exported "GDYXHD"."T_BSS_CUSTO_LOG"                  54.92 KB    1240 rows
. . exported "GDYXHD"."T_BSS_CUST_INFO_B"                13.42 KB      97 rows
Master table "GDYXHD"."SYS_EXPORT_SCHEMA_02" successfully loaded/unloaded
******************************************************************************
Dump file set for GDYXHD.SYS_EXPORT_SCHEMA_02 is:
  /u01/app/oracle/backup/dmp/t_bss_cust_info_par.dmp
Job "GDYXHD"."SYS_EXPORT_SCHEMA_02" successfully completed at 15:11:33

上面这个实验总结:
1.对于参数比较多的时候,可能一条操作系统命令太长而操作系统不允许,所以采用参数文件的方式没准是一个很好的方式,而且避免了转义字符带来的烦恼
2.如果只是指定table=....这样只能导出table类对象,如果使用include的话,那就允许同时导出表、索引、约束、等对象信息了。
如:include=table:"like 'T_BSS_CUST_%'",constraint:"like 'CKC_T%'"
这样控制的话,就更灵活了。
3.就是要注意table的名字要大写了,刚试了小写是识别不了的,因为Oracle在内部存储的就是大小名称

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

















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