How to export selective rows

You can export a selective rows of table with a new introduced QUERY option of export.You can use QUERY option on command line as well as in parameter file. Preferable way is to use parameter file.

Method 1 :- Command line

ON WINDOWS PLATFORM

Task:- Export only rows of table emp where comm. > 20

Construct the export command as follows :-

E:\ora816\BIN>exp scott/tiger file=c:\temp\emp.dmp log=c:\temp\emp.log tables=emp query="""where comm > 20"""

NOTE: You have to put three double quotes before and after the statement ="""where comm > 20""" . There should be NO spaces surrounding the double quotes and NO spaces between the double quotes either.

ON UNIX

Task:- Export only rows of table emp where comm. > 20 and ename = SAMEER

Construct the exp command as follows :-

$exp query=\" where comm \>20 and ename \= \'SAMEER\' \" tables=emp userid=scott/tiger

Note :- You have to put \ before each operator , surround non-numeric condition and at beginning and end of the query statement.

METHOD 2: USE OF PARAMETER FILE

This method is same on unix and nt.

Construnct the query clause in parfile as follows

Exppar.dat

file=expemp.dm

query="where enum > 12345"

tables=(emp)

log=emp.txt

userid=scott/tiger

You can also use subquery in the query clause as

Exppar.dat

file=expemp.dmp

query="where deptno in (Select deptno from dept where deptno in ('10','20'))"

tables=(emp)

log=emp.txt

userid=scott/tiger

Here the export is only for the employees who are in dept no 10 and 20 from dept table

exp parfile=exppar.dat