Oracle 10g 11g Data Pump EXPDP Query Parameter option

advertisements

_____________________________________________________________________________________________________________________

Datapump query option is used to export the subset of table data according to the WHERE filter clause. Please find the examples below.

EXPDP with parameter file- parfile

Suppose if you wanted to export 2 tables using where clause. For each table you can specify the where clause.

SQL> select count(*) from object_list where object_name like 'EIM%';

COUNT(*)

----------

2388224

Parfile Content:

userid="/ as sysdba"

job_name=query_export

query=test.OBJECT_LIST:"WHERE object_name like 'EIM%'", test.candidate:"WHERE NAME='James'"

tables=test.object_list, test.candidate

directory=EXP_DIR

dumpfile=QUERY_EXP.dmp

logfile=QUERY_EXP.log

$ expdp parfile=exp.par

Export: Release 11.2.0.2.0 - Production on Fri Jan 27 00:49:35 2012

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

Starting "SYS"."QUERY_EXPORT": /******** AS SYSDBA parfile=exp.par

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 2.678 GB

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

. . exported "TEST"."OBJECT_LIST" 218.6 MB 2388224 rows

. . exported "TEST"."CANDIDATE" 5.453 KB 1 rows

Master table "SYS"."QUERY_EXPORT" successfully loaded/unloaded

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

Dump file set for SYS.QUERY_EXPORT is:

/home/oracle/shony/QUERY_EXP.dmp

Job "SYS"."QUERY_EXPORT" successfully completed at 00:49:55

EXPDP Command line option with QUERY

$ expdp job_name=query_export query=test.OBJECT_LIST:\"WHERE object_name like \'EIM\%\'\", test.candidate:\"WHERE NAME=\'James\'\" tables=test.object_list, test.candidate directory=EXP_DIR dumpfile=QUERY_EXP.dmp logfile=QUERY_EXP.log

Export: Release 11.2.0.2.0 - Production on Fri Jan 27 01:10:15 2012

Username: / as sysdba