OraFAQ Forum: Server Utilities » Data pump Export with query and date value

Home » RDBMS Server » Server Utilities » Data pump Export with query and date value (Oracle 10gR2, Sun 5.10)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Data pump Export with query and date value [message #487324]

Fri, 24 December 2010 23:21

karthickbab
Messages: 16
Registered: November 2009
Location: India

Junior Member

Hi,
If i export data using thw below query it shows the error:

>expdp test1/test1 DIRECTORY=datapump DUMPFILE=expfull.dmp query=auth_test:\"where TXNREQDTTIME\<'20-MAY-10'\" tables=auth_test

bash-3.00$ expdp test1/test1 DIRECTORY=datapump DUMPFILE=expfull-3.dmp query=auth_test:\"where TXNREQDTTIME\<'20-MAY-10'\" tables=auth_test

Export: Release 10.2.0.1.0 - Production on Saturday, 25 December, 2010 5:10:06

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "TEST1"."SYS_EXPORT_TABLE_01": test1/******** DIRECTORY=datapump DUMPFILE=expfull-3.dmp query=auth_test:"where TXNREQDTTIME<20-MAY-10" tables=auth_test
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
ORA-31693: Table data object "TEST1"."AUTH_TEST" failed to load/unload and is being skipped due to error:
ORA-00904: "MAY": invalid identifierMaster table "TEST1"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST1.SYS_EXPORT_TABLE_01 is:
/App1/oracle/file/expfull-3.dmp
Job "TEST1"."SYS_EXPORT_TABLE_01" completed with 1 error(s) at 05:10:08

Anyone please solve this?

Report message to a moderator

Re: Data pump Export with query and date value [message #487327 is a reply to message #487324]

Sat, 25 December 2010 00:01

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

post formatted results of the following

SQL> DESC TEST1.AUTH_TEST

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

Report message to a moderator

Re: Data pump Export with query and date value [message #487328 is a reply to message #487327]

Sat, 25 December 2010 00:08

karthickbab
Messages: 16
Registered: November 2009
Location: India

Junior Member

SQL> desc test1.auth_test;
Name Null? Type
----------------------------------------- -------- ----------------------------
CARD_NO NUMBER
TXNREQDTTIME DATE
UNIQUEID VARCHAR2(40)

Report message to a moderator

Re: Data pump Export with query and date value [message #487330 is a reply to message #487328]

Sat, 25 December 2010 00:25

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>query=auth_test:\"where TXNREQDTTIME\<'20-MAY-10'\"
>query=auth_test:"where TXNREQDTTIME<20-MAY-10"

flawed for 1 or more reasons

With Oracle characters between single quote marks are STRINGS!
'This is a string, 2009-12-31, not a date'
When a DATE datatype is desired, then use TO_DATE() function.

Consider using a parameter file to avoid command line parsing of arguments

Report message to a moderator

Re: Data pump Export with query and date value [message #487336 is a reply to message #487330]

Sat, 25 December 2010 01:17

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

In addition, I think you have to also escape '.

Regards
Michel

Report message to a moderator

Previous Topic:	multiple rows
Next Topic:	How to import 11g data into 9i

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Dec 23 11:03:21 CST 2024