ITPub博客

首页 > Linux操作系统 > Linux操作系统 > exp/expdp中query参数的使用

exp/expdp中query参数的使用

原创 Linux操作系统 作者:wxjzqym 时间:2012-02-23 16:29:19 0 删除 编辑

    之前一直无法搞清楚exp/expdp中关于query参数的正确写法,每次都是零散的印象,这次特意把这个记录成日志以方便以后查看。关于query参数的写法请参看以下两个例子:
   1.使用命令行:

   
expdp t1/t1 directory=tl dumpfile=t2.dmp tables=t query="'where name=''a1'''"
Export: Release 10.2.0.4.0 - 64bit Production on Thursday, 23 February, 2012 16:27:41
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "T1"."SYS_EXPORT_TABLE_01":  t1/******** directory=tl dumpfile=t2.dmp tables=t query='where name=''a1'''
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "T1"."T"                                    5.234 KB       1 rows
Master table "T1"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for T1.SYS_EXPORT_TABLE_01 is:
  /home/oracle/tl/t2.dmp
Job "T1"."SYS_EXPORT_TABLE_01" successfully completed at 16:27:46
    2.使用参数文件:
cat par.txt
userid=t1/t1
file=t1.dmp
log=tl.log
tables=t
query='where name=''a1'''
[oracle@host101] ~/tl 1005>exp parfile=par.txt
Export: Release 10.2.0.4.0 - Production on Thu Feb 23 16:44:13 2012
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table                              T          1 rows exported
Export terminated successfully without warnings.

总结:如果在命令行中使用query,那么其中的条件需要用外层"加内层'组成,如果条件中有字符串,则字符串中的'号变成'';如果是将query写到参数文件中则只需要一层'即可,同样字符串中的'转换为''。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/20801486/viewspace-717031/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-05-15

  • 博文量
    100
  • 访问量
    458414