exp/expdp中query参数的使用

之前一直无法搞清楚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写到参数文件中则只需要一层'即可，同样字符串中的'转换为''。

总结

以上是生活随笔为你收集整理的exp/expdp中query参数的使用的全部内容，希望文章能够帮你解决所遇到的问题。

如果觉得生活随笔网站内容还不错，欢迎将生活随笔推荐给好友。