ORA-39001: invalid argument value-ORA-39035: Data filter SUBQUERY has already been specified.
Cause:
The error occur due to case sensitive characters used in query parameter of expdp
[oracle@orcl19x ~]$ expdp directory=DATAPUMP dumpfile=view_exp.dmp logfile=view_exp.log tables=sys.TEST query="\'where num > 2\'" REUSE_DUMPFILES=Y
Export: Release 12.1.0.2.0 - Production on Tue Jun 29 23:55:01 2021
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
ORA-39001: invalid argument value
ORA-39035: Data filter SUBQUERY has already been specified.
Same happens for include and exclude parameters as well without specifying object name
[oracle@orcl19x ~]$ expdp \'/ as sysdba\' directory=exp dumpfile=tabx.dmp logfile=tabx.log exclude=index,constraint,statistics
Export: Release 12.1.0.2.0 - Production on Fri Aug 13 18:42:19 2021
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01": "/******** AS SYSDBA" directory=exp dumpfile=tabx.dmp logfile=tabx.log exclude=index,constraint,statistics
Estimate in progress using BLOCKS method...
Total estimation using BLOCKS method: 0 KB
ORA-31655: no data or metadata objects selected for job
Job "SYS"."SYS_EXPORT_SCHEMA_01" completed with 1 error(s) at Fri Aug 13 18:42:44 2021 elapsed 0 00:00:20Workaround:
Check for the extra quotes and characters in query parameter
[oracle@orcl19x ~]$ expdp \'/ as sysdba\' directory=DATAPUMP dumpfile=view_exp.dmp logfile=view_exp.log tables=TEST.test query="'where num > 2'" REUSE_DUMPFILES=Y
Export: Release 12.1.0.2.0 - Production on Tue Jun 29 23:57:17 2021
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_01": "/******** AS SYSDBA" directory=DATAPUMP dumpfile=view_exp.dmp logfile=view_exp.log tables=TEST.test query='where num > 2' REUSE_DUMPFILES=Y
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
. . exported "TEST"."TEST" 5.617 KB 10 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
/data01/view_exp.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Tue Jun 29 23:57:43 2021 elapsed 0 00:00:20