Oracle Selective Data Export from Table using EXPDP (ORA-31693, ORA-39035, ORA-29913)

To do a selective data export in a table using expdp, query option can be used, provided if the where condition doesn’t have any subqueries.
For any Query Export Dumps, having Sub query should be using a parfile instead of the direct expdp command.
All the complex where condiitons can be written in a parfile and then the export can be kicked off.

The error codes ((ORA-31693, ORA-39035, ORA-29913)) are shown when we use, sub query in the expdp command with out using a par file. 

Example:

Parfile Content: item_par.par

directory=PUMP_DIR
dumpfile=ITEM_QUERY.dmp
logfile=ITEM.log
tables=ITEM
QUERY=ITEM:”where (item_id,price,EFFECTIVE_DATE)
in (select s.item_id, s.price, max(s.EFFECTIVE_DATE)
from dtv.ITEM s where item_id = s.item_id
and price = s.price and effective_date<=trunc(sysdate)
group by s.item_id, s.price)”

Usage:  expdp username/password parfile=item_par.par

As you see the log file, the total number of records in the ITEM table is 4434543.

But the filter condition fetches only 65711 rows, which is a time taking task if you need to select at Database level.

Note: If you have any question regarding this please comment below.