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

Share via:

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. 


Parfile Content: item_par.par

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. 


Share via:
Note: Please test scripts in Non Prod before trying in Production.
1 Star2 Stars3 Stars4 Stars5 Stars (13 votes, average: 5.00 out of 5)

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

  1. Hello,
    What if I want to export out from a table, data that is older than 3 months and delete this data afterwards….how do I script that process?


Add Comment