Oracle 10g 11g Data Pump EXPDP Query Parameter option

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

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

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

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

Username: / as sysdba

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

DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-14 All Rights Reserved | Site Map | Contact | Disclaimer | Google