Export Schema Minus Certain Tables

Oracle Data Pump export a schema minus the data for one table

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. 

See Also
Re-Organization Database Weekly Good or Bad

Have a Oracle Question
Do you have an Oracle Question?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Application
Oracle Application Hints and Tips

Oracle Home
Oracle Database, SQL, Application, Programming Tips

All the site contents are Copyright © www.erpgreat.com and the content authors. All rights reserved.
All product names are trademarks of their respective companies.
The site www.erpgreat.com is not affiliated with or endorsed by any company listed at this site.
Every effort is made to ensure the content integrity.  Information used on this site is at your own risk.
 The content on this site may not be reproduced or redistributed without the express written permission of
www.erpgreat.com or the content authors.