How to Exclude Data for Specific Tables in Datapump Import (IMPDP)

Sometimes it is necessary to exclude data for some tables in a datapump import. In this post we will look at how to do that.

SOLUTION

We will do this in two steps

1. Import with CONTENT=METADATA_ONLY option. This will create all the tables without data. Example

$ impdp  username/password DIRECTORY=<your_directory_name> DUMPFILE=<your_dump_file> CONTENT=METADATA_ONLY logfile=<your_log_file.log> SCHEMAS=<your_schema>

2. In the second import, create a parfile with EXCLUDE, and CONTENT=DATA_ONLY parameters.

The parfile is necessary to prevent ORA-39001, ORA-39071, and ORA-00936 when you specify the EXCLUDE option directly in the impdp command.

Sample parfile

$ cat imptbl.parfile
DUMPFILE=<your_dump_file>
DIRECTORY=<your_directory_name>
SCHEMAS=<your_schema>
EXCLUDE=TABLE:"IN ('<your_table_to_excldue_1>','<your_table_to_excldue_2>')"
CONTENT=DATA_ONLY

This will load the data into all the tables except those are specified in the excluded parameter.

Specify the parfile in the impdp command and execute.

$ impdp username/password  parfile=imptbl.parfile logfile=<your_log_file.log>

NB. Replace all entries in < > with your details.