Impdp tip : exclude content of a table and include metadata at once

I am currently in the process of migrating a 150 Gb Oracle 10g database to 12c. Due to some limitations I won’t discuss here, I have to do it with Datapump.
As I encountered different problems during the import, I had to launch it several times. It took 2 hours to import a 120 Gb table. I knew I had to rerun the import anyway to fix other issues, so I decided to get rid of the data of this big table temporarily.
I scratched my head for a few minutes, because I can be lazy and I did not want to run impdp twice just to exclude the content of the big table and include its metadata.


To avoid this situation, I just added the following line to my parameter file :

QUERY=(MY_SCHEMA.MY_BIG_TABLE:"WHERE 1=0")

And the magic happened :

impdp_query

The QUERY parameter lets you filter the data you want to import. It can be table-specific, or be applied to all the tables in the source file.
In my case, no rows where returned because the “WHERE 1=0” condition is never satisfied.

I think there are other methods to achieve the same goal but I find this one pretty nice and easy to understand.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s