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

at

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