Export/Import DataPump Parameter QUERY - How to Specify a Query (Doc ID 277010.1)

Last updated on NOVEMBER 28, 2016

Applies to:

Oracle Database - Standard Edition - Version 10.1.0.2 and later
Enterprise Manager for Oracle Database - Version 10.1.0.2 to 11.2.0.4 [Release 10.1 to 11.2]
Oracle Database - Enterprise Edition - Version 10.1.0.2 and later
Information in this document applies to any platform.
***Checked for relevance on 29-Apr-2014***

Goal

This document demonstrates how the QUERY parameter can be used with Export Data Pump (expdp) and Import Data Pump (impdp). It also shows where quotes must be used in the WHERE clause.

Incorrect usage of single or double quotes (or a space between the colon and the double quote) for the QUERY parameter can result in parse errors or errors such as:

LRM-00101: unknown parameter name 'empno'
ORA-39001: invalid argument value
ORA-39035: Data filter SUBQUERY has already been specified
ORA-31693: Table data object "SCOTT"."DEPT" failed to load/unload and is being skipped due to error:
ORA-00933: SQL command not properly ended
LRM-00111: no closing quote for value '' 
EXP-19: failed to process parameters, type 'EXP HELP=Y' for help
EXP-0: Export terminated unsuccessfully
ORA-31693: Table data object "SCOTT"."DEPT" 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

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms