Saturday, September 24, 2011

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:-)