Default: There is no default
Syntax and Description
query_clause is typically a SQL
WHERE clause for fine-grained row selection, but could be any SQL clause. For example, an
ORDER BY clause could be used to speed up a migration from a heap-organized table to an index-organized table. If a schema and table name are not supplied, then the query is applied to (and must be valid for) all tables in the source dump file set or database. A table-specific query overrides a query applied to all tables.
When the query is to be applied to a specific table, a colon (:) must separate the table name from the query clause. More than one table-specific query can be specified, but only one query can be specified per table.
NETWORK_LINK parameter is specified along with the
QUERY parameter, then any objects specified in the
query_clause that are on the remote (source) node must be explicitly qualified with the
NETWORK_LINK value. Otherwise, Data Pump assumes that the object is on the local (target) node; if it is not, then an error is returned and the import of the table from the remote (source) system fails.
For example, if you specify
NETWORK_LINK=dblink1, then the
query_clause of the
QUERY parameter must specify that link, as shown in the following example:
QUERY=(hr.employees:"WHERE last_name IN(SELECT last_name FROM hr.employees@dblink1)")
Depending on your operating system, the use of quotation marks when you specify a value for this parameter may also require that you use escape characters. Oracle recommends that you place this parameter in a parameter file, which can reduce the number of escape characters that might otherwise be needed on the command line. See "Use of Quotation Marks On the Data Pump Command Line".
QUERY parameter is used, the external tables method (rather than the direct path method) is used for data access.
To specify a schema other than your own in a table-specific query, you must be granted access to that specific table.
QUERY parameter cannot be used with the following parameters:
QUERY parameter is specified for a table, Data Pump uses external tables to load the target table. External tables uses a SQL
INSERT statement with a
SELECT clause. The value of the
QUERY parameter is included in the
WHERE clause of the
SELECT portion of the
INSERT statement. If the
QUERY parameter includes references to another table with columns whose names match the table being loaded, and if those columns are used in the query, then you will need to use a table alias to distinguish between columns in the table being loaded and columns in the
SELECT statement with the same name. The table alias used by Data Pump for the table being loaded is
For example, suppose you are importing a subset of the
sh.sales table based on the credit limit for a customer in the
sh.customers table. In the following example,
KU$ is used to qualify the
cust_id field in the
QUERY parameter for loading
sh.sales. As a result, Data Pump imports only rows for customers whose credit limit is greater than $10,000.
QUERY='sales:"WHERE EXISTS (SELECT cust_id FROM customers c WHERE cust_credit_limit > 10000 AND ku$.cust_id = c.cust_id)"'
KU$ is not used for a table alias, then all rows are loaded:
QUERY='sales:"WHERE EXISTS (SELECT cust_id FROM customers c WHERE cust_credit_limit > 10000 AND cust_id = c.cust_id)"'
The maximum length allowed for a
QUERY string is 4000 bytes including quotation marks, which means that the actual maximum length allowed is 3998 bytes.
The following is an example of using the
QUERY parameter. You can create the
expfull.dmp dump file used in this example by running the example provided for the Export
FULL parameter. See "FULL". Because the
QUERY value uses quotation marks, Oracle recommends that you use a parameter file.
Suppose you have a parameter file,
query_imp.par, that contains the following:
QUERY=departments:"WHERE department_id < 120"
You can then enter the following command:
> impdp hr DIRECTORY=dpump_dir1 DUMPFILE=expfull.dmp PARFILE=query_imp.par NOLOGFILE=YES
All tables in
expfull.dmp are imported, but for the
departments table, only data that meets the criteria specified in the
QUERY parameter is imported.