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.
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
andINCLUDE
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.