I have requirement to export multiple tables using datapump job and the ultimate goal is that we are doing a migration from on-Prem . They have something like 6TB of AUD data and we are not required to host all of that. We are trying to find a way to filter the AUD data in the export to only export and migrate the AUD data based upon a date to be determined.

Every AUD table has column change_date which can be used as filter. So based on the above requirement i have prepared the below job, but getting syntax error. Can you please help on this.

    INCLUDE=TABLE:"IN(select table_name from dba_tables where owner ='AUD';)" 
    query=\"where change_date > to_date('31-Dec-2020','DD-MON-YYYY')\"

I am receiving the following error:

-bash: syntax error near unexpected token `('
  • Don't escape quotes in query=\"where...
    – mustaccio
    Jul 12, 2022 at 18:10
  • I tried without the escape quote but still triggering the same error.
    – sarat
    Jul 13, 2022 at 1:39
  • @sarat remove the ; in your table filter, and make sure your to_date filter is accurate: your format is specified as 'MON' but your data is provided as 'Mon'.
    – pmdba
    Jul 13, 2022 at 10:54
  • Thanks for the update. I can able export single table using the below query parameter. TABLES=DUS.PUB_SETTINGS_REF_DND query=DUS.PUB_SETTINGS_REF_DND:" where change_date >= to_date('12/31/2020','MM/DD/YYYY')" But I have a set of 751 tables in (DUS schema) which I need to export using the above condition i.e., with change_date. So it is really painful to write 751 query statements. is there a possibility to take up with single command ?
    – sarat
    Jul 14, 2022 at 4:27

2 Answers 2


There're 3 types of character should be escaped from your command.

  • Double quotation marks
  • Single quotation marks
  • Round brackets, including left and right ones.

As for semicolon, it's unnecessary and error-prone, you must remove it from the command. To reduce escaping problems, you may consider using PARFILE.


You have a problem to wirte down the code appropriate for the shell. The simplest way to circumvent these problems is to write a parameter file for expdp that contains the parameters.

expdp system/xxxxxxx  parfile=<path of parameter file>

the text of the parameter file is

INCLUDE=TABLE:"IN(select table_name from dba_tables where owner ='AUD')"
query="where change_date > to_date('31-Dec-2020','DD-MON-YYYY')"

Note that you use single quotes (') to enclose string literals and double quotes (") to enclose parameters the are not oly single words.

If you want to use these parameter in the shell command line then you can do the following

expdp system/xxxxxxx   DIRECTORY=EXPDP_DIR \
    DUMPFILE=test.dmp \
    LOGFILE=test.log \
    INCLUDE=TABLE:"\"IN(select table_name from dba_tables where owner ='AUD')\"" \
    query="\"where change_date > to_date('31-Dec-2020','DD-MON-YYYY')\""

Note that I use the back slash () at the end of each line to avoid a very long line in the shell command line. Of course I can put all parameters in the same line instead.

A final remark: You already restrict your export to the AUD schema so there is not need to filter out the tables with owner='AUD' in the INCLUDE-clause. You can say that you only want to import tables. So finally the following INCLUDE parameter file is sufficient


You will get an error message for each table <TABLE_NAME> that does not contain a column CHANGE_DATE

ORA-31693: Table data object "AUD"."<TABLE_NAME>" failed to load/unload and is being skipped due to error:
ORA-00904: "CHANGE_DATE": invalid identifier

You can avoid these error messages by restricting your export to tables that contain a column CHANGE_DATE.

INCLUDE=TABLE:"IN(select table_name from dba_tab_columns where column_name='CHANGE_DATE')"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.