Export/Import DataPump Parameter QUERY Specification

I will try to demonstrate how you can use QUERY parameter in IMPDP or EXPDP. I will also try to show where quotes must be used in the WHERE clause. Incorrect usage of single or double quotes for the QUERY parameter can result in errors such as:

ORA-39001: invalid argument value
ORA-00933: SQL command not properly ended
LRM-00111: no closing quote for value ''
ORA-06502: PL/SQL: numeric or value error


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.
exp.par contains,

QUERY= prod.dept:"WHERE id IN (SELECT DISTINCT
id FROM prod.employees e, prod.jobs j WHERE e.job_id=j.job_id
AND UPPER(j.job_title) = 'DBA' OR e.salary >= 500)"

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.

Suppose in Unix,

expdp scott/tiger DIRECTORY=my_dir \
DUMPFILE=expdp.dmp LOGFILE=expdp.log TABLES=emp,dept \
QUERY=emp:\"WHERE ename LIKE \'A\%\'\"


3. QUERY in Oracle Enterprise Manager Database Console.
---------------------------------------------------------------
Login to EM>Maintenance>Utilities>Export to Files>Show Advanced Options(Step2)>QUERY>Add>...


The option in EM varies from version to version. In another version I saw it as,

Login to EM>Maintenance>Export to Export Files>Select options>Show Advanced Options>Query>Add>Table Name>Predicate Clause .............

4. Known Issues in Import/Export.
--------------------------------------
Bug 4383811 - Import Data Pump mail fail with: ORA-600[qerxtAgentOpen_911]
Bug 5767565 - Import Data Pump with REMAP_SCHEMA and NETWORK_LINK does not handle the QUERY parameter.

Related Documents:
--------------------
Data Pump Export/Import

Export/Import from Enterprise Manager

Original Export/Import