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:


(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:


(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”):


(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:


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:


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

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


All seems to be well now.

Have fun, and be careful

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.

5 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.

  3. Richard Says:

    Many thanks – I was about to break my keyboard when I discovered that last little gem about using a different escape character. I was having exactly the same issue with \. Sorted by simply replacing with !.

  4. Oracle Data Pump – parametri QUERY e EXCLUDE | Oracle and other Says:

    […] tentativi ed ho affinato le mie ricerche su internet fin quando finalmente sono arrivato a questo post che nella sua parte finale da un lato mi ha confermato che la sintassi che usavo era corretta […]

  5. scoTT Says:

    Thanks for this. I couldn’t figure out why certain tables were not being imported… all makes perfect sense now.

    With my excludes properly escaped all of the missing tables appeared!!

    Greatly appreciated.

Leave a Reply