Can we export Selected Row with Subquery using data pump I am trying to export selected rowS from my database using SUBQUERY but I got error.

expdp directory=exp_data dumpfile=selected_row.dmp logfile=rows.log tables=sample,super query="where name IN (select name from azeem.sample where name like 'Ten');"

Export: Release 11.2.0.4.0 - Production on Tue Jan 12 21:58:37 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39001: invalid argument value
ORA-39035: Data filter SUBQUERY has already been specified.


Can we use subquery in datapump?
Can we use subquery in datapump?


Yes we can. The following example shows how to do it.

I have created a parameter file to get this job done. In my test case I have two tables namely tbl1 and tbl2 under HR schema. You may change the name of the schema as your requirements.

[oracle@testsrv Desktop]$ cat query.par
Directory=user_dir
DUMPFILE=select_row.dmp
logfile=rows.log
tables=hr.tbl1, hr.tbl2
Query=hr.tbl1:"where name IN (select name from hr.tbl2 where name like '%T%')"


Then I have used the following expdp command to accomplish the task.

[oracle@testsrv Desktop]$ expdp system/oracle parfile=query.par

Export: Release 11.2.0.4.0 - Production on Mon Mar 21 15:59:18 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01":  system/password parfile=query.par 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "HR"."TBL1"                                     5 KB       0 rows
. . exported "HR"."TBL2"                                 5.007 KB       1 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
  /home/oracle/Desktop/select_row.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at Mon Mar 21 16:00:05 2016 elapsed 0 00:00:41