How to exclude certain tables from Oracle datapump export

Question

I have a large number of user schemas in my Oracle database. I wish to export the table data for these schemas but exclude any tables that are named with the second character an underscore.

For example I wish to export tables TPI_SUMMARY, DFXRRT and RTAFF but exclude C_NAMES, G_BEARS etc. Basically just want to exclude anything with an underscore as the second character

I am using Oracle data pump:

expdp system/xxxxx@string parfile=parfile.par

The parfile contains :

INCLUDE=TABLE:"IN(select table_name from dba_tables where table_name not like '_\_%')" 
EXCLUDE=SCHEMA:"='SYS','SYSTEM','SYSMAN','DBSNMP','OUTLN','APPQOSSYS'"
DIRECTORY=paul_test 
DUMPFILE=infrep_temp1.dmp 
FULL=Y 
LOGFILE=Daily_Backup_infrep_temp1.log

I just cannot get this to work. I think that the EXCLUDE bit is OK but the INCLUDE query is wrong. The DIRECTORY, DUMPFILE and LOGFILE are all OK.

I get the following:

Export: Release 11.2.0.4.0 - Production on Thu May 4 16:41:48 2017  

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.  

Connected to: Oracle Database 11g Release 11.2.0.4.0 - 64bit Production  
ORA-39001: invalid argument value  
ORA-39071: Value for EXCLUDE is badly formed.  
ORA-00933: SQL command not properly ended  

Any ideas? I'm really struggling to get this to work.

Answer 1

The error refers to EXCLUDE, not INCLUDE:

ORA-39071: Value for EXCLUDE is badly formed

... and it is indeed that which is wrong. You're trying to use equality with multiple values; you just need to use an IN() again:

EXCLUDE=SCHEMA:"IN('SYS','SYSTEM','SYSMAN','DBSNMP','OUTLN','APPQOSSYS')"

But as it says in the documentation:

The EXCLUDE and INCLUDE parameters are mutually exclusive.

You are allowed to use two EXCLUDE clauses though, so you can negate the first INCLUDE:

EXCLUDE=TABLE:"NOT IN(select table_name from dba_tables where table_name not like '_\_%')" 
EXCLUDE=SCHEMA:"IN('SYS','SYSTEM','SYSMAN','DBSNMP','OUTLN','APPQOSSYS')"

or as that's now a double-negative:

EXCLUDE=TABLE:"IN(select table_name from dba_tables where table_name like '_\_%')" 
EXCLUDE=SCHEMA:"IN('SYS','SYSTEM','SYSMAN','DBSNMP','OUTLN','APPQOSSYS')"

But that like doesn't match as you expect; you need to add an escape clause, and in this context you need to escape the backslash by doubling it up:

EXCLUDE=TABLE:"IN(select table_name from dba_tables where table_name like '_\_%' escape '\\')" 

or you could use substr() instead:

EXCLUDE=TABLE:"IN(select table_name from dba_tables where substr(table_name, 2, 1) = '_')"

You could also, if you have a list of schemas you do want, use two INCLUDE clauses instead - which might be simpler that listing all of the built-in schemas, which can vary.