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 :
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.