Oracle 11gR2 – Expdp Full Database With Exclude Some Table Data

All we need is an easy explanation of the problem, so here it is.

We will migrate our database using datapump.

It will be full database export with exclude some table’s datas.

But we want to do a different operation for 2 different tables. We want to export the data of the last 1 year for these 2 tables (let them be named table3 and table4). How can I write a query for this? The parfile I prepared is as follows, which commands should I add for these 2 tables?

DUMPFILE=dbname_datefull.dmp
LOGFILE=dbname_datefulllog.log
FULL=Y
DIRECTORY=EXPORT_DATE
EXCLUDE=STATISTICS
EXCLUDE=TABLE_DATA:"= 'table1'"
EXCLUDE=TABLE_DATA:"= 'table2'"
--queryfortable3
--queryfortable4
FLASHBACK_TIME=SYSTIMESTAMP

At the same time, does this query slow down the process and is it recommended?

Thank you. Best Regards.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

You must use the QUERY clause for those tables, something like this:

QUERY=table3:"WHERE date3>=TO_DATE('20190101','YYYYMMDD')",table4:"WHERE date4>=TO_DATE('20190101','YYYYMMDD')"

date3 and date4 being the date column of those tables.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply