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:20

Workaround:

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s