Imagine a database with two schemas, each with two tables with the same name:

How can I export the two schemas and exclude REPUSER.T1 – but keep APPUSER.T1?

Let’s Try

• I can use the EXCLUDE command line parameter:

  expdp schemas=APPUSER,REPUSER exclude="TABLE:\" = 'T1'\""
  

  • This filter removes all tables named T1 regardless of the schema.
  • This won’t work, because it also excludes APPUSER.T1.
  • The same applies if I use the filter in ('T1') or like ('T1').

• Can I add the schema to the filter?

  expdp schemas=APPUSER,REPUSER exclude=TABLE:"='REPUSER.T1'"
  

  • This filter causes Data Pump to exclude all tables named REPUSER.T1. It doesn’t interpret this as SCHEMA.TABLE_NAME.
  • The filter works solely on the table name. Internally, the filter is added to a query on a dictionary view and here schema and table name are two different columns. So, a single predicate won’t work.

• Currently, in Data Pump there is no way to shape the EXCLUDE parameter that meets the requirements. Bummer!

Solutions

• I can use two Data Pump jobs and use the filter only on REPUSER:

  expdp schemas=APPUSER
  expdp schemas=REPUSER exclude="TABLE:\" = 'T1'\""
  

  • In the interest of time, I can start the Data Pump jobs at the same time. I can even import the two dump files simultaneously.
  • Might not work if there are cross-schema dependencies.

• I can also export APPUSER.T1 later on:

  expdp schemas=APPUSER,REPUSER exclude="TABLE:\" = 'T1'\""
  expdp tables=APPUSER.T1
  

  • Although I can start the two exports at the same time, my table import has to wait for the first job to complete.

• Do you have any creative solutions? Leave a comment and let me know.

Happy exporting!