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

_____________________________________________________________________________________________________________________

0 comments:

Post a Comment

 

acehints.com Copyright 2011-17 All Rights Reserved | Site Map | Contact | Disclaimer | Google