How to exclude table data from Oracle Data Pump export

Posted by Ronald on May 15, 2014  /   Posted in Oracle, Oracle Backup & Recovery, Oracle Datapump

In a previous article I wrote about the REMAP_DATA option to skip column data when doing a datapump export. But what if you want from some of your tables only the table structure and all of its related objects but not the data? Well that’s easy to accomplish using Oracle datapumps QUERY option.

With this option, preferably specified in a parameter file, queries can be added when doing a datapump export like this:
QUERY=<schema>.<table_name>:"<condition>"
For example:
QUERY=ins_owner.aud_doc_document:"WHERE rownum < 1"
Using the “WHERE rownum < 1” clause no data will be exported for this table. In this way you can skip the data in tables that you don’t need but you will keep all table structure and related objects. For example when making a production copy you may not need audit and log data or confidential data and can easily skip this data using this method.

Comments are closed.

^ Back to Top