Oracle IMPDP: Wildcard hacking

Sometimes you want to use datapump import (impdp) for smart problems. An example is excluding (or including) a subset of objects, like tables. The usual way to do so is the EXCLUDE keyword.
(Footnote: All examples in this post are written for a parameter file, so don’t forget a proper quoting for your shell if you are using them on command line.)

(1) The basic syntax for excluding the table EMPLOYEES_1 from import is:

EXCLUDE=TABLE:EMPLOYEES_1

(2) More sophisticated is the use of an IN() statement for more than one table, let’s ignore EMPLOYEES_1, CARS_1 and TRUCKS_1:

EXCLUDE=TABLE:IN('EMPLOYEES_1','CARS_1','TRUCKS_1')

(3) But if you have to leave out a large(r) number of tables, maybe properly described by a pattern, EXCLUDE with IN() allows wildcards. This example prevents us from importing all tables ending with the literal ’1′ (“one”):

EXCLUDE=TABLE:IN('%1')

(4) But wildcards can be a curse, too. Especially since the underscore ‘_’ is not an uncommon part of an object’s name. In example 2 above, all tables are ending with ‘_1′. If you now want to distinguish between “ending with 1″ and “ending with _1″, you will experience some trouble, the underscore is the SQL wildcard for “one arbitrary character”, so most ‘%1′ patterns will match ‘%_1′ as well, but you want something entirely different. You need to escape the ‘_’ in a way, that impdp accepts the character as a literal.

The IN() of example 3 looks very much like SQL wildcarding, and it is. So my guess was, that escaping would work as described for the SQL like clause, with a user-defined escape character: select …. like ‘%!_1′escape’!’; transferred to impdp syntax:

EXCLUDE=TABLE:IN('%!_1'exclude'!')

but impdp heavily complains with

ORA-39001: invalid argument value
ORA-39071: Value for EXCLUDE is badly formed.
ORA-00907: missing right parenthesis
ORA-06512: at "SYS.KUPM$MCP", line 2687

So now it was hard to know what to do. But at some point, some olde bash instincts came through: Let’s escape with a backslash, and vóila:

EXCLUDE=TABLE:IN('%\_1')

that’s it! All tables ending with ‘_1′ are excluded from importing.

EDIT:
The last one seems to be buggy somehow, I finally tried this one:

EXCLUDE=TABLE:"LIKE '%!_1' ESCAPE '!'"

All seems to be well now.

Have fun, and be careful
Usn




You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

2 Responses to “Oracle IMPDP: Wildcard hacking”

  1. Joep Says:

    Thanks, especially your last ESCAPE did it!

  2. Eric Says:

    Thank you for posting this! I needed to exclude tables that begin with PS_G_ and your escape example solved my problem.

Leave a Reply