QUERY parameter in datapump

QUERY:

Enables you to filter the data that is exported by specifying a clause for a SQL SELECT statement, which is applied to all tables in the export job or to a specific table.

The QUERY parameter cannot be used in conjunction with the following parameters:

CONTENT=METADATA_ONLY

ESTIMATE_ONLY

TRANSPORT_TABLESPACES

DEMO:

SQL>  create table empquery as select * from scott.emp;

Table created.

SQL> select deptno from empquery;

    DEPTNO

----------

        20

        30

        30

        20

        30

        30

        10

        20

        10

        30

        20

    DEPTNO

----------

        30

        20

        10

SQL> create directory ipump as 'D:\';

Directory created.

SQL> grant read,write on directory ipump to scott;

Grant succeeded.

SQL> host expdp scott/tiger@delhi query=empquery:\"WHERE deptno>20\" directory=ipump

Export: Release 11.2.0.1.0 - Production on Sat Sep 24 11:46:58 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SCOTT"."SYS_EXPORT_SCHEMA_01":  scott/********@delhi query=empquery:"WHERE deptno>20" directory=ipump

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 320 KB

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/COMMENT

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

. . exported "SCOTT"."DEPT"                              5.937 KB       4 rows

. . exported "SCOTT"."EMP"                               8.570 KB      14 rows

. . exported "SCOTT"."EMPQUERY"                          8.265 KB       6 rows

. . exported "SCOTT"."SALGRADE"                          5.867 KB       5 rows

. . exported "SCOTT"."TEST"                              5.046 KB       5 rows

. . exported "SCOTT"."BONUS"                                 0 KB       0 rows

Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:

  D:\EXPDAT.DMP

Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 11:47:07

You see only 6 rows that are matching the criteria in the query clause are being exported.

Enjoy:-)