ORA-39071
Saw some errors including ORA-39071 and ORA-39001 when export some tables form the database.
[oracle@test ~]$ expdp system/password@orclpdb dumpfile=test.dmp logfile=test.log schemas=hr exclude=table:"in ('EMPLOYEES', 'DEPARTMENTS')"
...
ORA-39001: invalid argument value
ORA-39071: Value for EXCLUDE is badly formed.
ORA-00936: missing expression
Let's try to escape some characters, but we still got ORA-39071.
[oracle@test ~]$ expdp system/password@orclpdb dumpfile=test.dmp logfile=test.log schemas=hr exclude=table:\"in \('EMPLOYEES', 'DEPARTMENTS'\)\"
...
ORA-39001: invalid argument value
ORA-39071: Value for EXCLUDE is badly formed.
ORA-00904: "DEPARTMENTS": invalid identifier
ORA-39071 means that the value you passed into the parameter is not well-formed and unrecognized, you should check the format of parameter that you used.
Not only EXCLUDE, but also INCLUDE parameter has the same problem.
ORA-39071: Value for INCLUDE is badly formed.
In this case, we have escaped specials characters like double quotations and round brackets. The error ORA-00904 specifically complained about the identifier we passed into EXCLUDE parameter, which means, the problem is around the identifier.
Solution
What are around the identifier? Single quotation marks.
Therefore, we should additionally escape single quotes used to enclose identifiers to form the value well.
[oracle@test ~]$ expdp system/password@orclpdb dumpfile=test.dmp logfile=test.log schemas=hr include=table:\"in \(\'EMPLOYEES\', \'DEPARTMENTS\'\)\"
...
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/********@orclpdb dumpfile=test.dmp logfile=test.log schemas=hr include=table:"in ('EMPLOYEES', 'DEPARTMENTS')"
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
...
It works.
Don't forget to escape round brackets if there's any in your data pump command, otherwise you will see error -bash: syntax error near unexpected token `('.
To mitigate character escaping problems, Oracle recommends that we use PARFILE to hide parameters from the command line.