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 100+ 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 we 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 ..."'

More to Explore

Data Pump

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