Oracle export with date subsets 1

[Guest_imported]

Hello
I want to know if anyone can pls help me, I want to do a export of a table but only the records that has a date value > 01-JAN-2002, I tried the following :

exp usr/pwd tables=emp_tbl query="\where budget_date > to_date('01-JAN-2002','DD-MON-YYYY')\" but with no success.

Any help will be appreciated!
Thanks

jaco.vanderberg@webmail.co.za

 

[alexhu]

From the Oracle site

http://www.asktom.oracle.com

I find that Oracle8i can export table data selectively by using QUERY. Below is
the exp that I wrote for it. This is the first time I used it and was not
successful. The error message is:

table_export[2]: CUA_TRANS_DTS: not found.

Is there anything wrong in the syntax?



exp ddd/ddd file=/dbf/u11/customer.dmp tables=AASC.AST_CUSTOMER_KEEP
query=\'where CUA_TRANS_DTS \< add_months\(sysdate, -6\)\'


Best Regards




--------------------------------------------------------------------------------
and we said...

The method by which you specify the QUERY= parameter depends on the operating
system. A where clause generally has many special characters in it such as =, >
,<, and spaces. The shell command prompt in Unix and Windows is not going to
like those characters very much. They will have to be escaped and how to do
that is OS dependent. What I prefer to do is to always use a PARFILE with the
QUERY option. In this way, I can use the same exact methods regardless of
platform.

As an example of this, I have created a table T as select * from all_objects. I
want to export all rows such that the object_id is less then 5000. On Windows I
would have to execute:

C:\exp>exp userid=tkyte/tkyte tables=t query=&quot;&quot;&quot;where object_id < 5000&quot;&quot;&quot;

Note that we need 3 double quotes on each size of the where clause in Windows.
Now on Unix I must use:

$ exp userid=/ tables=t query=\&quot;where object_id \< 5000\&quot;

But, if I simply use a parameter file with this in it:

query=&quot;where object_id < 5000&quot;

I can now use the single command:

exp userid=/ tables=t parfile=exp.par

on both systems without change. I find that to be much easier then trying to
properly escape the QUERY strings on each platform.



Alex

 

[Guest_imported]

Thanks!
It worked perfectly!
Script is :
exp usr/pwd tables=(expenditure_tbl,budget_tbl) parfile=jaco.par grants=N com
press=Y file=csim_expend_budget.dmp

and the par file is :
query=&quot;where budget_date > to_date('01-JAN-2002','DD-MON-YYYY')&quot;

Thanks again