Using QUERY parameter in expdp/impdp

In data pump export import we can use SQl query to filter exported/imported data.
The parameter we use for this is 'QUERY'. But parameter QUERY requer proper formating (quotes)
Incorrect usage of single or double quotes for the QUERY parameter can result FOLLOWING errors:

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. USING 'QUERY' in Parameter file

The preferred method of using QUERY parameter is in a parameter file. Put double quotes around the WHERE clause.

parameter file exp_emp.par contains,

TABLES:emp_salary
DUMPFILE=dump_dir
QUERY= emp_salary:"WHERE emp_id IN (SELECT id from emp
where join_date> to_date('2004-01-01','YYYY-MM-DD')
AND dept='SALSE')"

2. USING 'QUERY' parameter on Command line

Suppose you want to create a subset of the table based on some criteria, e.g. “SALARY>10000”, you would issue

$ expdp query=employees:"where salary>10000" tables=employees

This can also take the ORDER BY clause to create the dumpfile in a sorted order. Suppose you want to dump the EMPLOYEES table order by SALARY, here is how the complete command looks like (with the unix required escape characters – backslahes):

$ expdp arup/arup directory=demo_dir dumpfile=employees.dmp
query=employees:\"where salary\>10000\ order by salary" tables=employees