Tuesday, September 16, 2008

Using QUERY parameter in expdp/impdp

In data pump export import we can use SQl query to filter exported/imported data.
The parameter we use for this is 'QUERY'. But parameter QUERY requer proper formating (quotes)
Incorrect usage of single or double quotes for the QUERY parameter can result FOLLOWING errors:

ORA-39001: invalid argument value
ORA-00933: SQL command not properly ended
LRM-00111: no closing quote for value ''
ORA-06502: PL/SQL: numeric or value error


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,

TABLES:emp_salary
DUMPFILE=dump_dir
QUERY= emp_salary:"WHERE emp_id IN (SELECT id from emp
where join_date> to_date('2004-01-01','YYYY-MM-DD')
AND dept='SALSE')"

2. USING 'QUERY' parameter on Command line

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

$ expdp query=employees:"where salary>10000" tables=employees

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

$ expdp arup/arup directory=demo_dir dumpfile=employees.dmp
query=employees:\"where salary\>10000\ order by salary" tables=employees

14 comments:

Bishwaranjan said...

Hello

Thanks for this topics. It really helped me.

Tamer ONEM said...

Hi Hasan ;
I have been struggling with some '\' dashes and punctioations. Thank you much.
Come and visit my blog too.
http://www.comtmr.blogspot.com/

FLC said...

I was too... be very careful with the escape character ... better test out first before executing... I was having problem and ended up extracting tonnes of data that was quickly exhausted my database space.. I blog about my database work and system analysis actvities too at http://flcwork.co.cc

Mark Wintle said...

It should read tables=, not tables:

Otherwise, this is a very useful example, thanks!

Mohammad Hasan Shaharear said...

@Mark Wintle

Are you sure ? I'm sure that the syntax 'TABLE:' is correct. please check the oracle doc

http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/dp_export.htm

Anjeet Kumar said...

try this

expdp hr/hr query="""where salary>15000""" tables=hr.employees directory=dir dumpfile=hr4.dmp

Anjeet Kumar said...
This comment has been removed by the author.
Shony said...

Good work. More examples here..
http://shonythomas.blogspot.in/2012/01/oracle-10g-11g-datapump-expdp-query.html

Oracle DBA said...

nice post n more can be found here :

http://chandu208.blogspot.com/2011/04/oracle-data-pump.html

TavoPlas said...

Hello, can you use "expdp" for export a table with column at any order and adding other columns null? i am using a ".par" file with:

QUERY=SCHEMA.TABLE1:'"SELECT COLUMN1, COLUMN2, COLUMN3, NULL,NULL,NULL,NULL,TO_CHAR(LASTDATE,'MM/DD/YYYY HH24:MI:SS')"'
NOLOGFILE=Y
DIRECTORY=MYDB
DUMPFILE=mytable_export.dmp

Ashish said...

in query optin can we use more condition for diff table individually?

Ashish said...

Hi,

Any Query Parameter If i want to use it for more than a tables with condition individually to each Table,Can we do it?

Ashish said...
This comment has been removed by the author.
Anonymous said...

Hi,

Any Query Parameter If i want to use it for more than a tables with condition individually to each Table,Can we do it?