export multiple tables using expdp with query condition

Question

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.

expdp 
    system/xxxxxxx 
    DIRECTORY=EXPDP_DIR 
    DUMPFILE=test.dmp 
    LOGFILE=test.log 
    CONTENT=DATA_ONLY 
    SCHEMAS=AUD 
    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 `('

Answer 1

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.

Answer 2

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

DIRECTORY=EXPDP_DIR 
DUMPFILE=test.dmp 
LOGFILE=test.log 
CONTENT=DATA_ONLY 
SCHEMAS=AUD 
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 \
    CONTENT=DATA_ONLY \
    SCHEMAS=AUD \
    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

INCLUDE=TABLE

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')"