single quote needed in expdp query?

本文涉及的产品
云原生数据仓库AnalyticDB MySQL版,基础版 8ACU 100GB 1个月
简介:
如果在使用数据泵时不采用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


相关实践学习
AnalyticDB MySQL海量数据秒级分析体验
快速上手AnalyticDB MySQL,玩转SQL开发等功能!本教程介绍如何在AnalyticDB MySQL中,一键加载内置数据集,并基于自动生成的查询脚本,运行复杂查询语句,秒级生成查询结果。
阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
云原生数据仓库AnalyticDB MySQL版是一种支持高并发低延时查询的新一代云原生数据仓库,高度兼容MySQL协议以及SQL:92、SQL:99、SQL:2003标准,可以对海量数据进行即时的多维分析透视和业务探索,快速构建企业云上数据仓库。 了解产品 https://www.aliyun.com/product/ApsaraDB/ads
相关文章
|
安全 关系型数据库 RDS
2-minute Comparison of Online Database and Self-built Databases
Should you use Alibaba Cloud ApsaraDB for RDS or build your own database?
2247 0
|
关系型数据库 数据库 数据安全/隐私保护
|
关系型数据库 MySQL 数据库
Table &#39;performance_schema.session_variables&#39; doesn&#39;t exist.
原因 需要更新mysql 措施 sudo mysql_upgrade -u root -p –force 出现以下内容成功 Checking server version. Running queries to upgrade MySQL server. Checking system database. ….. …..
1502 0