Oracle Data Pump – Export Selected Row with Subquery

oracleoracle-10goracle-11goracle-11g-r2

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?

Best Answer

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
Related Question