Oracle expdp … Export and then some…

The other day…no, not that day, the OTHER day, I got a call if it was possible to restore some tables from a database backup. This was no issue, until I saw the list of tables that where needed..

Table_a10.. table19,table_bcd0…table_bcd9 etc…

So I could do two things:

Create  parameter file in the form of

directory=DUMPME
dumpfile=DumpFile01.dmp
tables=(tablexx,tablexx,tablexx)

Possible, would work…but this involved a separate sql session, get the table names, cut and paste them in a file, do some editing..This has to be able to be done a lot smarter!

And yes, it is..The other way of doing this is to create a parameter File in the form of:

directory=DUMPME
dumpfile=DumpFile01.dmp
INCLUDE=TABLE:"IN(select table_name from user_tables where table_name like ’FOO%’ or table_name like ’BAR%’)"

The beauty of this is:

  • Because of using a parameter file, no need to exclude the backslashes with all kind of escapes as should be done when running this on the command line.
  • This syntax shows how to select multiple tables by using the “or”! There are enough examples on how to include a set of tables which are very similar, but this syntax shows how to select all kind of tables!

In short: This is a real gem!

Advertisements

About GemsOfProgramming

Beeing a previously enthusiastic Java programmer, I rolled into the Oracle Database Administration world. It turned out I got a knack for this, and since approx. 2000 I'm a full time DBA. My experiences touches lot of Oracle products like Forms and Reports 9/10, JDAPI, Application Server, Weblogic Fusion and of course: Oracle Enterprise Databases, JavaFX, Swing and other Java components.
This entry was posted in Databases, Technical Stuff and tagged , , , . Bookmark the permalink.

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