Home » RDBMS Server » Server Utilities » Syntax error in using query parameter in expdp (Oracle 11gr2 , Linux)
Syntax error in using query parameter in expdp [message #593476] Sat, 17 August 2013 02:04 Go to next message
Mohan10g
Messages: 159
Registered: May 2009
Location: INDIA
Senior Member

Hi,

I want to take an export of table MESSAGE, and filter it for the day of 17 JUL 2013 (just to limit the size). i used the following expdp command but its not working.
expdp SYSTEM directory=DATA_PUMP_DIR dumpfile=DB_16_08_2013.dmp logfile=FA0001P_BG_16_08_2013.log TABLES=schema.MESSAGE QUERY=schema.MESSAGE:where created_on between to_date('17-July-13 00:00:00','DD-Mon-YY hh24:MI:SS') and to_date('17-July-13 23:59:00','DD-Mon-YY hh24:MI:SS')

But with select query i am able to retrieve the rows for the specific date.
select * from MESSAGE where created_on between to_date('17-July-13 00:00:00','DD-Mon-YY hh24:MI:SS') and to_date('17-July-13 23:59:00','DD-Mon-YY hh24:MI:SS')

Here is the command with syntax error.
[oracle@orcl log]$ expdp SYSTEM directory=DATA_PUMP_DIR dumpfile=DB_16_08_2013.dmp logfile= DB_16_08_2013.log TABLES=schema.MESSAGE QUERY=schema.MESSAGE:where created_on between to_date('17-July-13 00:00:00','DD-Mon-YY hh24:MI:SS') and to_date('17-July-13 23:59:00','DD-Mon-YY hh24:MI:SS')
-bash: syntax error near unexpected token `('

Request you to correct the command.

Thanks

[Updated on: Sat, 17 August 2013 02:07]

Report message to a moderator

Re: Syntax error in using query parameter in expdp [message #593484 is a reply to message #593476] Sat, 17 August 2013 03:45 Go to previous messageGo to next message
Michel Cadot
Messages: 59748
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ You have to escape ( and ) as they are keywords for the shell: \(
2/ You have to enclose the whole QUERY parameter value between ": QUERY="schema.MESSAGE:\"...\""

Regards
Michel
Re: Syntax error in using query parameter in expdp [message #593487 is a reply to message #593484] Sat, 17 August 2013 09:58 Go to previous messageGo to next message
BlackSwan
Messages: 23029
Registered: January 2009
Senior Member
IMO, it is easier to accomplish the desired goal if all expdp parameters are placed with a/the control file.
When a control file is used, you avoid fighting with the command line interpreter.
Re: Syntax error in using query parameter in expdp [message #593611 is a reply to message #593487] Mon, 19 August 2013 03:56 Go to previous message
gazzag
Messages: 356
Registered: November 2010
Location: Bristol, UK
Senior Member
Blackswan,

Do you mean parameter file? Control file is something quite different in Oracle.
Previous Topic: Error using parfile
Next Topic: SQLLOADER value too large for column
Goto Forum:
  


Current Time: Sat Nov 22 22:58:08 CST 2014

Total time taken to generate the page: 0.10362 seconds