[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4117
  • Last Modified:

EXPDP ORACLE with INCLUDE and QUERY options

HI experts, I need do an export using expdp, with:
tables like A_5MIN%
and the cluase= WHERE UTCTIME BETWEEN '11/02/2014' AND '11/08/2014'
I tried with parfile:
userid=xajtdb/xxxxxxx@test
buffer=10000000
DIRECTORY=DATA_PUMP_DIR
DUMPFILE=export_tables_5min.dmp
rows=y
constraints=n
indexes=n
grants=n
statistics=none
INCLUDE=table:\"IN (\'A_5MIN_%\'\"
QUERY="WHERE UTCTIME BETWEEN '11/02/2014' AND '11/08/2014'"
LOGFILE=export_tables_5min.log

Open in new window

But I had problems and errors. Specially with the separator characters.
I'm working with SO Linux RedHat
It is posible do this?
Could you help me with some examples.
Regards
Thank you
0
carlino70
Asked:
carlino70
  • 3
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
You need to escape the quotes with \ like in the line before...
0
 
carlino70Author Commented:
Modifying this line like shows down:
INCLUDE=\"IN (\'A_5MIN_033\',\'A_5MIN_023\')\"
QUERY=\"WHERE UTCTIME BETWEEN \'11/02/2014\' AND \'11/08/2014\'\"

Open in new window

Shows the error:
Export: Release 11.2.0.3.0 - Production on Tue Aug 12 12:10:29 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
With the Automatic Storage Management option
ORA-39001: invalid argument value
ORA-39071: Value for INCLUDE is badly formed.
ORA-00907: missing right parenthesis

Open in new window

Any idea?
0
 
slightwv (䄆 Netminder) Commented:
Based on he docs the INCLUDE in the parfile doesn't need escaping:
http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_export.htm#i1007837

You also need LIKE not IN with a wildcard.  There is an example in the same doc link on the index line.

On the QUERY clause, I would look at using TO_DATE with the strings just to be safe.
0
 
carlino70Author Commented:
Excellent Sr. This is the parfile:
userid=xajtdb/neptune@xa21
buffer=10000000
DIRECTORY=DATA_PUMP_DIR
DUMPFILE=export_tables_5min.dmp
INCLUDE=TABLE:"LIKE 'A_5MIN_%'"
QUERY="WHERE UTCTIME BETWEEN TO_DATE('11/02/2014 00:00:00','DD/MM/YYYY HH24:MI:SS') AND TO_DATE('11/08/2014 23:59:59','DD/MM/YYYY HH24:MI:SS')"
LOGFILE=export_tables_5min.log

Open in new window

Thankyou again
0
 
carlino70Author Commented:
Excellent
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now