Got this request last week:
Can you send us an export of the schema, only without any data for the log_events table (because it is big and they don’t really need it for what they’re doing.)
Doing a full schema export without any data, or with only data, no problem, just use CONTENT=. Can I export and just have one table that I don’t get data for. Well, I could export, import somewhere, truncate the table, export again. No too much trouble … then it hit me, an old trick. Just be untrue about it.
So, here is what I did, and it worked just great.
expdp schemas=ess_owner dumpfile=ess.dmp logfile=ess_exp.log query='ess_owner.log_events:"where 1=2"'
Basically, do a regular export of the schema but use the QUERY clause to get only rows that match an inequality (thus no rows). I’ve used this technique before in queries (such as the “create table as select … with no rows”) and fortunately with Data Pump you can do this too. You can add additional tables if you need more without data.
Or
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.
