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:
EXCLUDE=TABLE:"LIKE '%!_1' ESCAPE '!'"
All seems to be well now.
Have fun, and be careful