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 `('

• mustaccio: Don't escape quotes in query=\"where...
• sarat: I tried without the escape quote but still triggering the same error.
• pmdba: @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'.
• sarat: 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 ?

Answer 1

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

Answer 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.

Answer 3

Can you try this in your parfile:

exclude=table:"IN('TABLE_NAME')"
TABLES = SCHEMA_NAME.TABLE_NAME1,SCHEMA_NAME.TABLE_NAME2
query = SCHEMA_NAME.TABLE_NAME1:"WHERE CONDITION1 LIKE '<1>'",SCHEMA_NAME.TABLE_NAME2:"WHERE CONDITION2 LIKE '<1>'"
DIRECTORY=DBFS_DIR
filesize=5G
dumpfile=SCHEMA_NAME_%U.dmp
logfile=SCHEMA_NAME_SOURCE.log