Export/Import DataPump Parameter QUERY - How to Specify a Query
The examples below are based on the following demo schema's:
- user SCOTT created with script: $ORACLE_HOME/rdbms/admin/scott.sql
- user HR created with script: $ORACLE_HOME/demo/schema/human_resources/hr_main.sql
The Export Data Pump and Import Data Pump examples that are mentioned below are based on the directory my_dir. This directory object needs to refer to an existing directory on the server where the Oracle RDBMS is installed. Example:
-- for Windows platforms:
CONNECT system/manager
CREATE OR REPLACE DIRECTORY my_dir AS 'D:\export';
GRANT read,write ON DIRECTORY my_dir TO public;
-- for Unix platforms:
CONNECT system/manager
CREATE OR REPLACE DIRECTORY my_dir AS '/home/users/export';
GRANT read,write ON DIRECTORY my_dir TO public;
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]
3. QUERY in Oracle Enterprise Manager Database Console.
The QUERY can also be specified in the Oracle Enterprise Manager Database Console. E.g.:
- Login to the Oracle Enterprise Manager 10g Database Console, e.g.: http://my_node_name:5500/em
- Click on link 'Maintenance'
- Under 'Utilities', click on link 'Export to Files'
- Answer questions on the following pages.
- At 'step 2 of 5' (the page with the Options), click on link 'Show Advanced Options'
- At the end of the page, under the QUERY option, click on button 'Add'
- At the next page, choose the table name (SCOTT.EMP)
- And specify the SELECT statement predicate clause to be applied to tables being exported, e.g.: WHERE ename LIKE 'A%'
- Continue with the remaining options, and submit the job.
4. Import Data Pump parameter QUERY.
Similar to previous examples with Export Data Pump, the QUERY parameter can also be used during the import. An example of how to use the QUERY parameter with Import Data Pump:
-- In source database:
-- Export the schema SCOTT:
%expdp scott/tiger DIRECTORY=my_dir DUMPFILE=expdp_s.dmp \
LOGFILE=expdp_s.log SCHEMAS=scott
-- In target database:
-- Import all employees of department 10:
%impdp scott/tiger DIRECTORY=my_dir DUMPFILE=expdp_s.dmp \
LOGFILE=impdp_s.log TABLES=emp TABLE_EXISTS_ACTION=append \
QUERY=\"WHERE deptno = 10\" CONTENT=data_only
Note that this feature was not available with the original import client (imp). Also note that the parameter TABLE_EXISTS_ACTION=append is used to allow the import into an existing table and that CONTENT=data_only is used to skip importing statistics, indexes, etc.
