Export/Import in Datapump using query parameter

Purpose

Enables you to filter the data that is exported by specifying a clause for a SELECT statement, which is applied to all/multiple tables in the export job or to a specific table.

Solution

[oracle@said ~]$ cat bak.sh
expdp system/passwd@orcl parfile=/home/oracle/parameter.par
[oracle@said ~]$


[oracle@said ~]$ cat parameter.par
tables=SCOTT.EMP
query=SCOTT.EMP:"WHERE (liability_issued - nvl(liability_reversed, 0) != 0
or  paid_margin_amount_lcy - nvl(refunded_margin_lcy, 0) != 0
or  indem_liability_issued - nvl(indem_liability_reversed, 0) != 0)"
directory=dpump
dumpfile=emp.dmp
logfile=emp.log
[oracle@said ~]$  ./back.sh

Export: Release 11.2.0.3.0 - Production on Thu Mar 16 14:39:45 2017

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01":  system/********@orcl parfile=/home/oracle/parameter.par
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 683.1 GB
Processing object type TABLE_EXPORT/TABLE/TABLE

.

.

.

.

.

.