Skip to Main Content
  • Questions

  • IMPDP statement with multiple where and table clause

Breadcrumb

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 1000+ times

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 ..."'


Is this answer out of date? If it is, please let us know via 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 latest video and Chris's latest video 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.