single quote needed in expdp query?

如果在使用資料泵時不采用parfile參數檔案的話，query參數指定的查詢條件是需要使用單引号括起來的，而當使用parfile時則不需要加上單引号，加上後反而會出現LPX-314: an internal failure occurred錯誤:

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. * 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: 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 > 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 $ 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]