single quote needed in expdp query?

简介:
如果在使用数据泵时不采用parfile参数文件的话,query参数指定的查询条件是需要使用单引号括起来的,而当使用parfile时则不需要加上单引号,加上后反而会出现LPX-314: an internal failure occurred错误:
[maclean@rh2 mesg]$ expdp maclean/maclean directory=dump  tables=estimate_me query=estimate_me:"where t1<2000"

Export: Release 10.2.0.4.0 - 64bit Production on Tuesday, 02 November, 2010 22:32:33

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
FLASHBACK automatically enabled to preserve database integrity.
Starting "MACLEAN"."SYS_EXPORT_TABLE_01":  maclean/******** directory=dump tables=estimate_me query=estimate_me:where t1<2000
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
ORA-31693: Table data object "MACLEAN"."ESTIMATE_ME" failed to load/unload and is being skipped due to error:
ORA-00936: missing expression
Master table "MACLEAN"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for MACLEAN.SYS_EXPORT_TABLE_01 is:
  /s01/dump/expdat.dmp
Job "MACLEAN"."SYS_EXPORT_TABLE_01" completed with 1 error(s) at 22:32:36

/* 不采用参数文件形式,没有加单引号的情况下出现ORA-00936: missing expression错误*/

[maclean@rh2 mesg]$ expdp maclean/maclean directory=dump  tables=estimate_me query=estimate_me:'"where t1<2000"'

Export: Release 10.2.0.4.0 - 64bit Production on Tuesday, 02 November, 2010 22:33:39

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
FLASHBACK automatically enabled to preserve database integrity.
Starting "MACLEAN"."SYS_EXPORT_TABLE_01":  maclean/******** directory=dump tables=estimate_me query=estimate_me:"where t1<2000"
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "MACLEAN"."ESTIMATE_ME"                     32.42 KB    1999 rows
Master table "MACLEAN"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for MACLEAN.SYS_EXPORT_TABLE_01 is:
  /s01/dump/expdat.dmp
Job "MACLEAN"."SYS_EXPORT_TABLE_01" successfully completed at 22:33:43

/* 不采用参数文件,在query参数中加上单引号则导出成功 */

[maclean@rh2 mesg]$ cat quote.par
tables=estimate_me
query=estimate_me:'"where t1<2000"'
directory=dump

[maclean@rh2 mesg]$ rm /s01/dump/expdat.dmp

[maclean@rh2 mesg]$ expdp maclean/maclean parfile=quote.par

Export: Release 10.2.0.4.0 - 64bit Production on Tuesday, 02 November, 2010 22:35:08

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
FLASHBACK automatically enabled to preserve database integrity.
Starting "MACLEAN"."SYS_EXPORT_TABLE_01":  maclean/******** parfile=quote.par
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
ORA-31693: Table data object "MACLEAN"."ESTIMATE_ME" failed to load/unload and is being skipped due to error:
ORA-06502: PL/SQL: numeric or value error
ORA-31605: the following was returned from LpxXSLSetTextVar in routine kuxslSetParam:
LPX-314: an internal failure occurred
Master table "MACLEAN"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for MACLEAN.SYS_EXPORT_TABLE_01 is:
  /s01/dump/expdat.dmp
Job "MACLEAN"."SYS_EXPORT_TABLE_01" completed with 1 error(s) at 22:35:11

/* 采用parfile选项,参数文件中query参数加入单引号出现LPX-314: an internal failure occurred错误,导出失败*/

[maclean@rh2 mesg]$ cat quote.par
tables=estimate_me
query=estimate_me:"where t1<2000"
directory=dump
[maclean@rh2 mesg]$ rm /s01/dump/expdat.dmp
[maclean@rh2 mesg]$ expdp maclean/maclean parfile=quote.par

Export: Release 10.2.0.4.0 - 64bit Production on Tuesday, 02 November, 2010 22:36:27

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
FLASHBACK automatically enabled to preserve database integrity.
Starting "MACLEAN"."SYS_EXPORT_TABLE_01":  maclean/******** parfile=quote.par
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "MACLEAN"."ESTIMATE_ME"                     32.42 KB    1999 rows
Master table "MACLEAN"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for MACLEAN.SYS_EXPORT_TABLE_01 is:
  /s01/dump/expdat.dmp
Job "MACLEAN"."SYS_EXPORT_TABLE_01" successfully completed at 22:36:30

/* 采用parfile,参数文件中query未加入单引号,导出成功 */

1. QUERY in Parameter file. Using the QUERY parameter in a parameter file is the preferred method. Put double quotes around the text of the WHERE clause. Example to export the following data with the Export Data Pump client: * from table scott.emp all employees whose job is analyst or whose salary is 3000 or more; and * from from table hr.departments all deparments of the employees whose job is analyst or whose salary is 3000 or more. File: expdp_q.par ----------------- DIRECTORY = my_dir DUMPFILE = exp_query.dmp LOGFILE = exp_query.log SCHEMAS = hr, scott INCLUDE = TABLE:"IN ('EMP', 'DEPARTMENTS')" QUERY = scott.emp:"WHERE job = 'ANALYST' OR sal >= 3000" # place following 3 lines on one single line: QUERY = hr.departments:"WHERE department_id IN (SELECT DISTINCT department_id FROM hr.employees e, hr.jobs j WHERE e.job_id=j.job_id AND UPPER(j.job_title) = 'ANALYST' OR e.salary >= 3000)" -- Run Export DataPump job: %expdp system/manager parfile=expdp_q.par Note that in this example the TABLES parameter cannot be used, because all table names that are specified at the TABLES parameter should reside in the same schema. 2. QUERY on Command line. The QUERY parameter can also be used on the command line. Again, put double quotes around the text of the WHERE clause. Example to export the following data with the Export Data Pump client: * table scott.dept; and * from table scott.emp all employees whose name starts with an 'A' -- Example Windows platforms: -- Note that the double quote character needs to be 'escaped' -- Place following statement on one single line: D:\> expdp scott/tiger DIRECTORY=my_dir DUMPFILE=expdp_q.dmp LOGFILE=expdp_q.log TABLES=emp,dept QUERY=emp:\"WHERE ename LIKE 'A%'\" -- Example Unix platforms: -- Note that all special characters need to be 'escaped' % expdp scott/tiger DIRECTORY=my_dir \ DUMPFILE=expdp_q.dmp LOGFILE=expdp_q.log TABLES=emp,dept \ QUERY=emp:\"WHERE ename LIKE \'A\%\'\" -- Example VMS platform: -- Using three double-quote characters $ expdp scott/tiger DIRECTORY=my_dir - DUMPFILE=exp_cmd.dmp LOGFILE=exp_cmd.log TABLES=emp,dept - QUERY=emp:"""WHERE ename LIKE 'A%'""" Note that with the original export client two jobs were required: -- Example Windows platforms: -- Place following statement on one single line: D:\> exp scott/tiger FILE=exp_q1.dmp LOG=exp_q1.log TABLES=emp QUERY=\"WHERE ename LIKE 'A%'\" D:\> exp scott/tiger FILE=exp_q2.dmp LOG=exp_q2.log TABLES=dept -- Example Unix platforms: > exp scott/tiger FILE=exp_q1.dmp LOG=exp_q1.log TABLES=emp \ QUERY=\"WHERE ename LIKE \'A\%\'\" > exp scott/tiger FILE=exp_q2.dmp LOG=exp_q2.log TABLES=dept -- Example VMS platform: $ exp scott/tiger FILE=exp_q1.dmp LOG=exp_q1.log TABLES=emp - QUERY="""WHERE ename LIKE 'A%'""" $ exp scott/tiger FILE=exp_q2.dmp LOG=exp_q2.log TABLES=dept Note that with original export client in Oracle8i on VMS, the syntax was different (also note the extra space that is needed between two single quotes): ... QUERY="'WHERE ename LIKE \'A%\' '" That is: [double_quote][single_quote]WHERE ename LIKE [backslash][single_quote]A%[backslash][single_quote][space][single_quote][double_quote]

本文转自maclean_007 51CTO博客,原文链接:http://blog.51cto.com/maclean/1277595


相关实践学习
数据库实验室挑战任务-初级任务
本场景介绍如何开通属于你的免费云数据库,在RDS-MySQL中完成对学生成绩的详情查询,执行指定类型SQL。
阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
云原生数据仓库AnalyticDB MySQL版是一种支持高并发低延时查询的新一代云原生数据仓库,高度兼容MySQL协议以及SQL:92、SQL:99、SQL:2003标准,可以对海量数据进行即时的多维分析透视和业务探索,快速构建企业云上数据仓库。 了解产品 https://www.aliyun.com/product/ApsaraDB/ads
相关文章
|
SQL 关系型数据库 MySQL
Exploiting hard filtered SQL Injections
http://websec.wordpress.com/2010/03/19/exploiting-hard-filtered-sql-injections/ While participa...
1164 0
|
SQL 数据库
Database specific hint in One order search
Database specific hint in One order search
109 0
Database specific hint in One order search
How to find where settype DB table COMM_PRMAT is accessed without debugging
How to find where settype DB table COMM_PRMAT is accessed without debugging
113 0
How to find where settype DB table COMM_PRMAT is accessed without debugging
automatic asynchronous creation if no note exists
Created by Wang, Jerry, last modified on May 12, 2015
104 0
automatic asynchronous creation if no note exists
|
Oracle 关系型数据库
Mandatory Patching Requirement for Database Versions 11.2.0.3 or Earlier, Using DB Links (DOC ID 2335265.1)
Mandatory Patching Requirement for Database Versions 11.2.0.3 or Earlier, Using DB Links (DOC ID 2335265.
2770 0
|
安全 关系型数据库 RDS
2-minute Comparison of Online Database and Self-built Databases
Should you use Alibaba Cloud ApsaraDB for RDS or build your own database?
2227 0
|
测试技术 关系型数据库 Oracle
[20171106]DBMS_UTILITY.GET_TIME().txt
[20171106]DBMS_UTILITY.GET_TIME().txt --//有时候测试某个脚本运行时间,经常在这之前之后调用这个函数.今天奇怪的发现显示竟然是负数,感觉很奇怪做一个探究.
1013 0