Wednesday, June 11, 2008

How to use Exclude in EXPDP:

Recently i got a requirement to export schema minus certain tables:
expdp system/******** schemas=REPORT directory=REPORT1 dumpfile=report1.dmp logfile=report1.log EXCLUDE=TABLE:\"LIKE \'OP%\'\", TABLE:\"LIKE \'%XYZ%\'\"

I figured out the power of expdp here, which enables the use of LIKE and such other operations in the EXCLUDE section.
I had problems with just typing EXCLUDE=TABLE:"LIKE 'OP%'", TABLE:"LIKE '%XYZ%'":

ORA-39001: invalid argument value
ORA-39071: Value for EXCLUDE is badly formed.
ORA-00911: invalid character

So decided to include escape characters in the statement, so it became:
EXCLUDE=TABLE:\"LIKE \'OP%\'\", TABLE:\"LIKE \'%XYZ%\'\" , this means exclude tables starting with OP and any tables which have XYZ in their table name.


Other options in expdp exclude:

Using the NOT IN OPERATOR
EXCLUDE=TABLE:\"NOT IN \(\'TEMP\',\'TEMP1\'\)\"

Using the IN OPERATOR
EXCLUDE=TABLE:\"IN \(\'TEMP\',\'TEMP1\'\)\"

I think this is a great advancement in the way we use exp. I found this very helpful.