Skip to Main Content
  • Questions

  • IMPDP statement with multiple where and table clause

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.

Question and Answer

Chris Saxon

Thanks for the question, Sunny.

Asked: November 21, 2020 - 6:18 am UTC

Last updated: November 23, 2020 - 3:08 pm UTC

Version: 12c R2

Viewed 10K+ times! This question is

You Asked

I have more than 20 tables to restore from exported dumpfile. so
i have question how to import 20 tables into one impdp statement so my dba can save time.
here i post two different impdp statement which contain different where statement and different tables to be import.

impdp username/password DIRECTORY=DATA_PUMP_DIR DUMPFILE=dmp_file_name.dmp LOGFILE=dmp_log_file.txt TABLES=HR.emp_log query=HR.emp_log:\"where dept_id in  ( select a.dept_id from HR.remote_data_emp_log a where a.log_date = '31-DEC-2019' ) \"
impdp username/password DIRECTORY=DATA_PUMP_DIR DUMPFILE=dmp_file_name.dmp LOGFILE=dmp_log_file.txt TABLES=HR.dept_log query=HR.dept_log:\"where dept_id in  ( select a.dept_id from HR.remote_data_dept_log a where a.dept_log_date = '31-DEC-2019' ) \"


and Chris said...

As the docs say:

When you want to apply the query to a specific table, you must separate the table name from the query cause with a colon (:). You can specify more than one table-specific query , but only one query can be specified per table.

https://docs.oracle.com/en/database/oracle/oracle-database/19/sutil/datapump-import-utility.html#GUID-B00723CC-C649-49FC-8C3F-CA5925045F4F

So just provide a comma-separated list of the queries, each colon-prefixed with the name of the source table e.g.:

query='t1:"WHERE ...",t2:"WHERE ..."'


We're not taking comments currently, so please try again later if you want to add a comment.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Data Pump

All of the database utilities including Data Pump are explained in the Utilities guide.