Oracle DBA Tips and Techniques

By Arjun Raja

QUERY BASED EXPORT USING DATAPUMP – EXPDP

Posted by Arjun Raja on April 26, 2012

Run a query based export using expdp.

Confirm rows to be exported by running a normal query.

SQL> SELECT COUNT (*) FROM EMP WHERE LOAD_DATE= TRUNC (SYSDATE -190);

COUNT(*)
———-
42

vi exparj.par

directory=adhoc
dumpfile=arjuntest1.dmp
logfile=arjuntest1.log
COMPRESSION=ALL
CONTENT=ALL
TABLES=”ARJUN.EMP”
QUERY= ARJUN.EMP:”WHERE LOAD_DATE = TRUNC (SYSDATE -190)”

nohup expdp arjun/arjun parfile=exparj.par &

Export: Release 11.2.0.2.0 – Production on Thu Apr 26 08:39:33 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, OLAP, Data Mining and Real Application Testing options
Starting “ARJUN”.”SYS_EXPORT_TABLE_01″:  arjun/******** parfile=exparj.par
Estimate in progress using BLOCKS method…
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 366 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported “ARJUN”.”EMP”                           1.728 MB      42 rows
Master table “ARJUN”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for ARJUN.SYS_EXPORT_TABLE_01 is:
/u03/oradata/export/arjuntest1.dmp
Job “ARJUN”.”SYS_EXPORT_TABLE_01″ successfully completed at 08:39:55

Export completed.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: