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