[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
