Datapump Query Parameter

Data pump Query Parameter.

Datapump query option is used to export the subset of table data according to the WHERE filter clause. Please find the examples below.

1. USING 'QUERY' in Parameter file

The preferred method of using QUERY parameter is in a parameter file. Put double quotes around the WHERE clause.

parameter file exp_emp.par contains,

    Directory = dump
      dumpfile=d1.dmp
      Tables='SCOTT . TEST '
      query=TEST:\" WHERE deptno = 20 \"
    

 hosted with ❤ by GitHub

Method 1 : parameter file- parfile

• Create Table Test. (Create table scott.test as select * from scott.emp;)

• Create Parameter File. (Par File ).

     Directory = dump
      dumpfile=d1.dmp
      Tables='SCOTT . TEST '
      query=TEST:\" WHERE deptno = 20 \"
     -----------------------------------------------------------------
      Save this file in Test.par

 hosted with ❤ by GitHub

• Go to command prompt.

   Export Command

      expdp system/admin parfile='/u01/app/parag/mydump/test.par'

  After Export Goto Sql Prompt

   Sql > Drop Table Scott.TEST;

  After Drop Table Goto Command Prompt

  Import Command

    impdp system/admin directory=dump dumpfile=d1.dmp tables='SCOTT.TEST'

  After Importing this table Goto Sql Prompt.

   Sql > Select * from Scott.Test;

 hosted with ❤ by GitHub

2. USING 'QUERY' parameter on Command line

Suppose you want to create a subset of the table based on some criteria, e.g. “deptno>10”, you would issue

$ expdp query=TEST:"where 'deptno >10 '\" tables='SCOTT.TEST'

This can also take the ORDER BY clause to create the dumpfile in a sorted order. Suppose you want to dump the TEST table order by DEPTNO, here is how the complete command looks like (with the unix required escape characters – backslahes):

$ expdp system/admin directory=dump dumpfile=d2.dmp

query=TEST:\"where deptno\>10\ order by deptno" tables='SCOTT.TEST'

Method 2 : 

• Create Table Test. (Create table scott.test as select * from scott.emp;)
• Go to command prompt.

•    Export Command

        expdp system/admin directory=dump dumpfile=d2.dmp tables='SCOTT'.TEST'      query=TEST:\" WHERE 'deptno >10'\"

  
  

    After Export Goto Sql Prompt

  
  

     Sql > Drop Table Scott.TEST;

  
  

    After Drop Table Goto Command Prompt

  
  

    Import Command

      impdp system/admin directory=dump dumpfile=d1.dmp tables='SCOTT.TEST'
  
  

    After Importing this table Goto Sql Prompt.

  
  

     Sql > Select * from Scott.Test;

   hosted with ❤ by GitHub