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 troube … 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.

Advertisements

4 responses to “Oracle Data Pump export a schema minus the data for one table

  1. Thanks, this exactly what I have been looking for. we need to skip history/logs tables. I will try this in datapump

  2. Thanks alot , Its awesome man, never thought this trick ever existed 🙂 .
    I usually use it in query too creating another table based from source table but without rows like 1=0 which is never true.

    Keep it up 🙂 , also thanks for your dataguard paper. It cover up lots details thats im looking for weeks.

  3. Jed, thank you so much for this, I’ve been thrown into the deep end as a backup Oracle DBA (my prior experience was just MySQL). This syntax helped us copy a large production DB to an XE test/dev system.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s