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:
Hello
Thanks for this topics. It really helped me.
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/
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
It should read tables=, not tables:
Otherwise, this is a very useful example, thanks!
@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
try this
expdp hr/hr query="""where salary>15000""" tables=hr.employees directory=dir dumpfile=hr4.dmp
Good work. More examples here..
http://shonythomas.blogspot.in/2012/01/oracle-10g-11g-datapump-expdp-query.html
nice post n more can be found here :
http://chandu208.blogspot.com/2011/04/oracle-data-pump.html
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
in query optin can we use more condition for diff table individually?
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?
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?
Post a Comment