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

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

  1. Gokhan Atil

    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

  3. Paul Moore

    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…

  5. adam

    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%\’ \)”

  8. me_lucky

    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’))”

  9. Pingback: Drop table cascade and reimport – Laurent Schneider

  10. AtheistMonk

    Thank you adam!, I’m using a parameter file and Gokhan’s version wasn’t working in this case.

  12. Laurent Schneider Post author

    1) use a parameter file
    2) use backslash and quote and doublequote until it works, depending on your operating system
    3) put the table list in a small table and use Gokhan approach (see above)

Comments are closed.