Generate consistent backup through Oracle Datapump export

The original export utility when setting with consistent=y will create the export dump file of the database objects from the point in time to the beginning of the export session.

Conventional Export

exp userid="/ as sysdba" owner= file=.dmp log=.log consistent=y

However we don’t have have consistent parameter for datapump export. By default datapump export is consistent, but not for the entire dump file.It is consistent for one table at a time and not for the entire export.

We can use FLASHBACK_SCN or FLASHBACK_TIME parameters to achieve the consistency of the backup. Find the below equivalent commands for consistent backups in both original export and datapump export.

Datapump Export

vi expdp.par

dumpfile= .dmp
logfile= .log

expdp userid="/ as sysdba" parfile=expdp.par

Data Migration using Oracle Datapump QUERY option

Consider the case when we want to migrate the selected records from source database to target database we have the below great technique available onwards 10g

I used QUERY option of Oracle Datapump tool for satisfy the requirements of Data Migration from source database to target database.

1. Taking backup using Oracle Datapump tool from source database
2. Move the backup on target database
3. Import the backup


1. Create and configure database link in target database
2. Taking backup using Oracle Datapump tool from source database and import on target database using network_link option

expdp scott/******* parfile=mstpara.par

-bash-4.1$ cat mstpara.par
query=EMP:"where dept = '505'"


Oracle Data Pump: Interactive Mode – Attach/Detach

I had a customer who was importing a long running Data Pump import job. The client needed to go home and still have a way to monitor the import job.

Starting with Oracle 10g Data Pump there is a useful feature in situations in which you start a job at one location and need to check on it at a later time from a different location