About Me

My photo
Bangalore, India
I am an Oracle Certified Professional working in SAP Labs. Everything written on my blog has been tested on my local environment, Please test before implementing or running in production. You can contact me at amit.rath0708@gmail.com.

Friday, December 13, 2013

Query Parameter in Datapump (EXPDP/IMPDP) utility

Sometimes we need to export or imprt only some data from a huge table. This can be done by using query parameter in Datapump utility. Query parameter can be used in both EXPDP as well as IMPDP utility.

1. EXPDP :-

Query parameter can be used in two ways  :-

a. Using parameter file :-

bash-3.2$
bash-3.2$ cat test.par
TABLES=AMIT
directory=datapump
DUMPFILE=expdp_test.dmp
logfile=expdp_test.log
query=amit:"where DATE > '01-DEC-2013'"
bash-3.2$

bash-3.2$ expdp user/user parfile=test.par

 Export: Release 11.2.0.1.0 - Production on Fri Dec 13 12:20:47 2013

 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 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "USER"."SYS_EXPORT_TABLE_01":   user/****** parfile=test.par
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 384 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "USER"."AMIT"             30.67 KB      75 rows
Master table "USER"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for USER.SYS_EXPORT_TABLE_01 is:
  /disk1/datapump/expdp_etst.dmp
Job "USER"."SYS_EXPORT_TABLE_01" successfully completed at 12:20:53

b. In Command Line :-

expdp user/user directory=datapump dumpfile=expdp_taa_01.dmp logfile=expdp_taa_01.log query=amit:\"where DATE \> \'01-DEC-2013\'\" tables=AMIT

 Export: Release 11.2.0.1.0 - Production on Fri Dec 13 12:24:50 2013

 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 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "USER"."SYS_EXPORT_TABLE_01":  user/******** directory=datapump dumpfile=expdp_taa_01.dmp logfile=expdp_taa_01.log query=amit:"where DATE > '01-DEC-2013'" tables=AMIT
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 384 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "USER"."AMIT"             30.67 KB      75 rows
Master table "USER"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for USER.SYS_EXPORT_TABLE_01 is:
  /disk1/datapump/expdp_taa_01.dmp
Job "USER"."SYS_EXPORT_TABLE_01" successfully completed at 12:24:56

 2. IMPDP :-

 a. Using par file :-

bash-3.2$ cat test.par

schemas=DATA
include=TABLE:"IN('TEST')"
directory=datapump
DUMPFILE=expdp_fullDB_12_Dec_13.dmp
logfile=expdp_etst.log
query=test:"where DATE > '01-DEC-2013'"
remap_schema=DATA:AMIT

 bash-3.2$ impdp amit/amit parfile=test.par

 Import: Release 11.2.0.1.0 - Production on Fri Dec 13 12:44:22 2013

 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 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "AMIT"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "AMIT"."SYS_IMPORT_SCHEMA_01":  amit/******** parfile=test.par
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
. . imported "AMIT"."TEST"                    51.17 KB      75 out of 1614 rows
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
Job "AMIT"."SYS_IMPORT_SCHEMA_01" successfully completed at 12:44:44

b. In Command line :-

bash-3.2$ impdp amit/amit directory=datapump dumpfile=expdp_fullDB_12_Dec_13.dmp logfile=test.log query=TEST:\"where DATE \> \'01-DEC-2013\'\" schemas= DATA  include=TABLE:\"IN\(\'TEST\'\)\" remap_schema=DATA:AMIT

 Import: Release 11.2.0.1.0 - Production on Fri Dec 13 12:38:29 2013

 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 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "AMIT"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "AMIT"."SYS_IMPORT_SCHEMA_01":  amit/******** directory=datapump dumpfile=expdp_fullDB_12_Dec_13.dmp logfile=test.log query=test:"where DATE > '01-DEC-2013'" schemas=DATA include=TABLE:"IN('TEST')" remap_schema=DATA:AMIT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
. . imported "AMIT"."TEST"                    51.17 KB      75 out of 1614 rows
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
Job "AMIT"."SYS_IMPORT_SCHEMA_01" successfully completed at 12:38:55

I hope this article helped you.

 Regards,
Amit Rath

Posted by at
Labels: , ,

6 comments:

  1. PJadhavJuly 22, 2015 at 9:17 PM

    if I have 1000 tables in schema and I want to export data affected in time period of last week (from 12 july to Todays date) .Then How expdb parameters can used ?

    ReplyDelete
    Replies
    1. Amit RathJuly 27, 2015 at 7:32 AM

      Hi Jadhav,

      you should have a column in all tables which have the modified date detail with them, then you can use the query parameter to export all tables in that specified range.

      For eg: if schema name = AMIT and modified date column name is "MODIFIED" in all tables, then you can use below in EXPDP options

      schemas=AMIT
      QUERY="WHERE MODIFIED>'01-MAY-2015'"
      DIRECTORY=DP_DUMP
      DUMPFILE=expdp_dump.dmp

      Hope this helps

      Thanks
      Amit Rath

      Delete
  2. AnonymousJuly 8, 2016 at 2:24 PM

    Thanx a lot, it was very helpfull for me to avoid importing a 30Gb table that will took hours, with QUERY parameter I could be able to only select the data I needed and import in minutes.

    ReplyDelete
  3. Khalid Oracle BlogFebruary 11, 2018 at 3:25 PM

    Thanks nice article, helped me to resolve one scenario.

    ReplyDelete
  4. HimaFebruary 26, 2018 at 9:56 PM

    Its really useful post with proper info. thanks..!!

    ReplyDelete
  5. Jagannath MaharanaMay 25, 2021 at 4:42 PM

    Many many thanks Amit.

    ReplyDelete

Subscribe to: Post Comments (Atom)