Datapump : table like ‘FOO%’ or like ‘BAR%’

Today I tried to put two like condition in an INCLUDE clause of datapump.

I have the following tables

SQL> select table_name from user_tables order by 1;

TABLE_NAME
------------------------------
AAA
BAR1   ***
BAR2   ***
BLA
FOO    ***
FOO1   ***
GOZ

and I want tables like BAR% and tables likes FOO%

First try :

$ expdp scott/tiger include=table:"like'FOO%'or like'BAR%'"

Export: Release 11.2.0.2.0 - Production on Thu Jul 14 11:47:13 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39001: invalid argument value
ORA-39071: Value for INCLUDE is badly formed.
ORA-00936: missing expression

This does not work, because it would translate in WHERE {tablename} like’FOO%’or like’BAR%’

Ok, second try, let’s put multiple TABLE clause

$ expdp scott/tiger include=table:"like'FOO%'",table:"like'BAR%'"

Export: Release 11.2.0.2.0 - Production on Thu Jul 14 11:47:15 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01":  scott/******** include=table:"like'FOO%'",table:"like'BAR%'" Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
ORA-39168: Object path TABLE was not found.
ORA-31655: no data or metadata objects selected for job
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" completed with 2 error(s) at 11:47:21

no data was found, because it did translate in WHERE {tablename} like ‘FOO%’ and{tablename} like ‘BAR%’. Which returns no row.

Ok, multiple INCLUDE conditions are joined by AND, so let’s do the math. (BAR% OR FOO%)=(>=BAR AND <FOP AND NOT BETWEEN BAS AND FONZZZ (where FONZZZ is immediately smaller than FOO)

$ expdp scott/tiger include=table:">='BAR'",table:"not between 'BAS' and 'FONZZZZZZZZZZZZZ'",table:"<'FOP'"

Export: Release 11.2.0.2.0 - Production on Thu Jul 14 11:47:21 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01":  scott/******** include=table:">='BAR'",table:"not between 'BAS' and 'FONZZZZZZZZZZZZZ'",table:"<'FOP'"
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 256 KB
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . exported "SCOTT"."BAR1"                             5.007 KB       1 rows
. . exported "SCOTT"."BAR2"                             5.007 KB       1 rows
. . exported "SCOTT"."FOO"                              5.007 KB       1 rows
. . exported "SCOTT"."FOO1"                             5.007 KB       1 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
  /u01/app/oracle/admin/DB01/dmp/expdat.dmp
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 11:47:31

It is that simple ;)

8 thoughts on “Datapump : table like ‘FOO%’ or like ‘BAR%’”

  1. Hi Laurent,

    We can also use the “old” subquery trick:

    include=table:”in (select table_name from user_tables where table_name like ‘FOO%’ or table_name like ‘BAR%’ )”

    for example (special chars are escaped because of linux prompt):

    expdp directory=DATA_PUMP_DIR dumpfile=gokhan.dmp include=table:\”in \(select table_name from user_tables where table_name like \’FOO%\’ or table_name like \’BAR%\’ \)\”

    Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    Starting “GOKHAN”.”SYS_EXPORT_SCHEMA_01″: gokhan/******** directory=DATA_PUMP_DIR dumpfile=gokhan.dmp include=table:”in (select table_name from user_tables where table_name like ‘FOO%’ or table_name like ‘BAR%’ )”
    Estimate in progress using BLOCKS method…
    Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
    Total estimation using BLOCKS method: 0 KB
    Processing object type SCHEMA_EXPORT/TABLE/TABLE
    . . exported “GOKHAN”.”BAR1″ 0 KB 0 rows
    . . exported “GOKHAN”.”BAR2″ 0 KB 0 rows
    . . exported “GOKHAN”.”FOO1″ 0 KB 0 rows
    . . exported “GOKHAN”.”FOO2″ 0 KB 0 rows
    Master table “GOKHAN”.”SYS_EXPORT_SCHEMA_01″ successfully loaded/unloaded

    Regards

    Gokhan

  2. Alternatively, switch it round to an exclude:

    exclude=table:”not like ‘FOO%'”,table:”not like ‘BAR%'”

    Now you just have to get your head round the double negatives…

  3. Thanks , Gokhan and Laurent .
    If you are using a parfile don’t escape the double quotes.
    include=table:”in \(select table_name from user_tables where table_name like \’FOO%\’ or table_name like \’BAR%\’ \)”

  4. Hi Gokhan Atil

    Am using the quey like , its giving me error ORA-39071.
    include=table:”in (select table_name from dba_tables where (table_name like ‘%_D%’ or table_name like ‘%_DS%’ escape ‘/’ or table_name like ‘QRT%’ )and a.owner IN (‘X’,’Y’))”

Leave a Reply

Your email address will not be published.


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>