Server Utilities :: Syntax Error In Using Query Parameter In Expdp

Aug 17, 2013

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 `('

View 3 Replies


ADVERTISEMENT

Server Utilities :: EXPDP With Query Error?

Jun 3, 2010

While trying to expdp using Query logics, getting syntax related erros shown below:

expdp system/xxxx SCHEMAS=LOG NETWORK_LINK=DBLINK1 INCLUDE=TABLE:"IN('DAILY_LOG')" QUERY=LOG.DAILY_LOG:"where entry_date< to_char(sysdate -1,'yyyymmdd')" DIRECTORY=dump DUMPFILE=log_exp.dmp logfile=log_exp.log

But gives the following error
ORA-31693: Table data object "LOG"."DAILY_LOG" failed to load/unload and is being skipped due to error:
ORA-00904: "YYYYMMDD": invalid identifier

I tried with simple sql with YYYMMDD and it works fine, the entry_date is a char field. in QUERY where i'm doing wrong here?

View 4 Replies View Related

Server Utilities :: SQL*Loader-350 Syntax Error When Parsing Data Type

Dec 7, 2010

I ran the following control file in sql loader:

LOAD DATA
INFILE "gateway.csv"
truncate
INTO TABLE GATEWAY
Fields terminated by ","
Optionally enclosed by '"'
trailing nullcols

[code]....

and I got the following error:

zcyds891:/opt/oracle> sqlldr gwcem/gwcem@pfs control=gateway.ctl log=/tmp/ldr.log bad=/tmp/bad.log
SQL*Loader: Release 9.2.0.8.0 - Production on Tue Dec 7 05:07:59 2010
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL*Loader-350: Syntax error at line 12.
Expecting "," or ")", found "INTERGER".
GATEWAYPROTOCOL INTERGER,
^

The current OS is Solaris, SunOS 5.10.

View 3 Replies View Related

Server Utilities :: Error During Expdp On Oracle 11gR2 On Solaris?

Dec 2, 2010

From some day I have this error during export data pump:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-31626: job does not exist
ORA-31687: error creating worker process with worker id 1
ORA-31687: error creating worker process with worker id 1
ORA-31688: Worker process failed during startup.

This error is random, if I retry after few minutes the expdp work correctly.

View 8 Replies View Related

Server Utilities :: Query Parameter - Export Subset Of Table Using RowID

Aug 15, 2012

I am exporting using query parameter. I am trying to export subset of table using rowid.

SQL> select rowid , name from tab1;

ROWID NAME
------------------ ---------------
AAAM0rAAEAAAAGMAAA sam
AAAM0rAAEAAAAGMAAB sona
AAAM0rAAEAAAAGMAAC rose
AAAM0rAAEAAAAGMAAD chris
AAAM0rAAEAAAAGMAAE san
.................. ....
.................. ....

command given as

exp sam/sam tables=tab1 file=exprwid.dmp query="where ROWID='AAAM0rAAEAAAAGMAAA'" log=log1.log

Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table TAB1
EXP-00056: ORACLE error 911 encountered
ORA-00911: invalid character
Export terminated successfully with warnings.

how can i export this record ?

View 4 Replies View Related

Server Utilities :: Do EXPDP Utilities Does Backup At Block Level As What RMAN Is Doing

May 29, 2013

I have one doubt on Expdp & RMAN. Do EXPDP utilities does backup at block level as what RMAN is doing? Which one is faster, expdp or RMAN?

View 16 Replies View Related

Export/Import/SQL Loader :: Expdp Command To Export Table By Specifying Query Parameter

Aug 16, 2013

I am using expdp command to export the table by specifying Query parameter. But i am unable to export the table based on the condition. 

Ex:EXPDP username/password dumpfile=employee.dmp logfile=emp.log directory=DATADIR_EXP TABLES=EMPLOYEE query=EMPLOYEE:"UPDATED_TIME >= '04-JUN-13' AND UPDATED_TIME >= '05-AUG-13'" Estimate in progress using BLOCKS method...Processing object type TABLE_ EXPORT/ TABLE/ TABLE_ DATATotal estimation using BLOCKS method: 3 GBORA-31693: Table data object "<username>"."EMPLOYEE" failed to load/unload and is being skipped due to error:ORA-00933: SQL command not properly endedMaster table "<username>"."EMPLOYEE" successfully loaded/unloaded...Dump file set for <username>.SYS_EXPORT_TABLE_01 is:  E:IMPDPemployee.dmpJob "<username>"."SYS_EXPORT_TABLE_01" completed with 1 error(s) at 12:34:45  Oracle 11g,

View 6 Replies View Related

Server Utilities :: Syntax For Exporting Only Procedures Of Particular User

Mar 7, 2011

What is the syntax for exporting only the procedures of a particular user.

View 1 Replies View Related

Server Utilities :: Export Sequence Using Expdp

Apr 13, 2011

I would like to export specific tables(not entire schema) including metadata. I am using a parameter file for expdp.

Tables=emp,dept

Does this also include all metadata or should i also add the below Include in the parfile ?

INCLUDE =Indexes,Sequences,Procedures,Views

View 3 Replies View Related

Server Utilities :: Slow Expdp After Upgrade

Oct 1, 2012

We had AIX OS on 570 machine and database 10.2.0.4. We took expdp and it took 2 and hour to complete every night.

Now we upgrade to 10.2.0.5 and 770 machine and now same command takes 6 hours to complete even database and hardware is upgraded

Command is

expdp T24SILK/oracle directory=backup dumpfile=exp_beod_T24_%U_$dt
.dmp logfile=exp_T24_$dt.log EXCLUDE=TABLE:"LIKE '%TRACE'" parallel=6

View 1 Replies View Related

Server Utilities :: Expdp Hangs In Oracle 11g?

May 27, 2011

I am trying to export Schema using expdp command. but its going hang after few minutes. it seems that it stucks any where. Even I am trying with normal scott schema it is also hanging.

View 16 Replies View Related

Server Utilities :: Performance Diff In Exp And Expdp?

Jun 4, 2010

I export a table using exp utility it take 30 mins to complete the export.The same i have done in expdp utility it take 10 mins to complete the export.

How it happens?

View 3 Replies View Related

Server Utilities :: Excluding Data Of Some Tables In EXPDP

Oct 5, 2013

i want to exclude only data of some particular tables not complete table object when exporting using expdp.

View 13 Replies View Related

Server Utilities :: Expdp Displays Output In German

Aug 26, 2012

I have a server configured to German & English. when i connect with SQLPLUS, i have German language server output, but when i do "alter session set nls_language='AMERICAN'" - it solves the issue for me.

I need the same for expdp command, but I don't know how to do this. I have tried to add a parameter nls_language, but expdp doesn't recognize it. Is it possible to somehow see server output of the expdp & writing it to the log file in English?

View 5 Replies View Related

Server Utilities :: Data Pump Expdp To ASM Daily?

Jun 16, 2011

i succeeded to expdp to ASM diskgroup such as

create directory asmexpdir as '+RECO/FILTDB/EXPDP';
grant read,write on directory asmexpdir to oraasfs;
expdp oraasfs/oraasfs2301 directory=asmexpdir dumpfile=SBSR_EXP.dmp tables=TM_SFS_CUST_01 logfile=EXPDP_LOG:SBSR_EXP.log

SUCCESS MESSAGE

. . exported "ORAASFS"."TM_SFS_CUST_01" 387.2 MB 817684 rows
Master table "ORAASFS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ORAASFS.SYS_EXPORT_TABLE_01 is:
+RECO/filtdb/expdp/sbsr_exp.dmp
Job "ORAASFS"."SYS_EXPORT_TABLE_01" successfully completed at 03:34:59

And I like to run this daily and delete after 14 days. but it show error, what can be the solution to run this script?

#!/bin/bash
#Script to Perform Datapump Export backup Every Day
################################################################
#Change History

[code]...

View 9 Replies View Related

Translating Query From SQL Server To Oracle Syntax

Sep 10, 2009

Translate following SQL query from SQL Server syntax to Oracle syntax.

SELECT ID,
[LMT(MTR)] = MAX(case when TYPE = 'LMT' then VALUE end),
[AAD(KGM)] = MAX(case when TYPE = 'AAD' then VALUE end),
[VOL(MTQ)] = MAX(case when TYPE = 'VOL' then VALUE end)
FROM yourtable
GROUP BY ID

View 2 Replies View Related

Server Utilities :: Export Data Using Expdp To Remote Host

Jun 5, 2012

I have to servers 'A' and 'B', On Server there is a schema with the name "test" having a table "t1". I want to import this t1 table to server B.

Is it possible to export dump using expdp to remote host.

I found that there is an option for this like "network_link". for testing this, I created a dblink from Server "B" to "A" named "vxmldb".

When I am using the below command on Server B there I am getting the following error.

C:>expdp directory=data_pump_dir logfile=test.log network_link=vxmldb schemas=test dumpfile=test.dmp

Export: Release 11.1.0.6.0 - 64bit Production on Tuesday, 05 June, 2012 14:22:07
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Username: system/vxmldb@vxmldb
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39001: invalid argument value
ORA-39170: Schema expression 'TEST' does not correspond to any schemas.

In above command

directory ---> Server "B" location
network_link ----- > dblink name which is created on Server "B" to access Server "A"
schemas ------ > schema name which is to be exported . Exists on Server "A" DB
username/password ---- >> higher level username/password for Server "A".
@connectString ----- >> connecting to Server "A"

View 15 Replies View Related

Server Utilities :: Export / Dump With Expdp For Non-privileged Users?

May 29, 2012

We are DB users (not DBAs) and used always exp/imp bevore application upgrade.

Was googling arround and read something like "Oracle Data Pump - Time to let go of Exp / Imp". It seems exp/imp is obsolete.

Our system doesn't have "expdp" command

> find . -name expdp
>

is this because of too old SQL*Plus?

> sqlplus
SQL*Plus: Release 8.1.7.0.0 - Production on Tue May 29 16:05:28 2012
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Enter user-name: ^C^C

- does our DBA need to give us privileges to run expdp/impdp?

- is that true that a expdp/impdp dump will be on the Oracle server (not the client machine)?

View 4 Replies View Related

Server Utilities :: Using Expdp To Exclude The Stream Objects While Doing Import?

Feb 1, 2012

I am having one prod and one devl with prod having stream setup.

I have to refresh devl with prod , but if i will go by full expdp then db_links also get imported into the devl and may cause problem in devl.

Is there any other way using expdp to exclude the stream objects while doing import.

View 1 Replies View Related

Server Utilities :: Expdp Using Remap_schema Will It Also Remap Grants And Synonyms

May 1, 2010

If I would be using expdp using remap_schema will it also remap grants and synonyms ?

View 5 Replies View Related

Server Utilities :: Overwrite Existing Dump File In Expdp In Oracle 10g?

Apr 13, 2012

How we can overwrite existing dump file for expdp in oracle 10g because everytime we excute expdp and dmp file exist we get below error

ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31641: unable to create dump file "C:scott_emp.dmp"
ORA-27038: created file already exists
OSD-04010: <create> option specified, file already exists

We have one feature in 11g reuse_dumpfiles=y ,which doesnt work in 10g, I want something which can overwrite existing dumpfile in 10g?

View 1 Replies View Related

Server Utilities :: Using Expdp / Impdp To Migrate 4 TB Database From Solaris To Linux

Aug 4, 2011

I am using expdp/impdp to migrate 4 TB database from solaris to Linux.But the import process is taking forever.

View 13 Replies View Related

Server Utilities :: Taking Export Dump Using Expdp Of Some Schema's Of Total Size Is 300GB

Mar 30, 2007

I'm taking export dump using expdp of some schema's of total size is 300GB. This is the par file:

DIRECTORY=expdp
FILESIZE=32212254720
DUMPFILE=expdp_schema01.dmp,expdp_schema02.dmp,expdp_schema03.dmp,expdp_schema04.dmp,expdp_schema05.dmp,expdp_schema06.dmp,expdp_sche ma07.dmp,expdp_schema08.dmp,expdp_schema09.dmp,expdp_schema10.dmp,expdp_schema11.dmp,expdp_schema12.dmp,expdp_schema13.d
[code]....

here one biggest schema size is 250GB and the total size of all the schema's is 300GB. The file where am taking the dump has 350GB space but even then the expdp failed saying

ORA-39095: Dump file space has been exhausted: Unable to allocate 8192 bytes

why it failed and how to restart it and make sure it runs successfully without error.

View 4 Replies View Related

SQL & PL/SQL :: Syntax For Optional ALL Parameter In Oracle SQL

Apr 17, 2012

I am trying to pass an Optional 'ALL' Parameter to Oracle in the SQL statement below...

USER.STATUS can be either 0 or 1 in the Source-Data (Inactive or Active). However, the :P_STATUS Parameter can be either 0, 1 or 2 (ALL).

I tried an IF/THEN/ELSE statement in the SQL below - but it doesn't work as is.

Gives and "ORA-00920: invalid relational operator" error...
-------------------
SELECT
i.LAST_NAME SURNAME,
i.FIRST_NAME GIVEN_NAME,
DECODE(u.STATUS, 1, 'Active', 0, 'Inactive') STATUS,
u.STATUS STATUS_CODE
[code].......

View 1 Replies View Related

Server Utilities :: Want To Convert Exp Script To Expdp Script

Jul 27, 2011

I am having export script which performs normal export operaion on full database. As no I want to convert the same script with expdp (datapump). So what are the changes I need to take care.As per my knowledge I have to perform following task:

1. Create a dicrectory
2. change command from exp to expdp with directory name mentioned.

View 2 Replies View Related

Server Utilities :: How To Create Parameter Files

May 20, 2011

How to create parameter files i.e. (.par) files for expdp/impdp..

View 1 Replies View Related

Server Utilities :: Import With Buffer Parameter

Jan 12, 2011

We are having daily syncing script which use to drop and import of one major schema.

When we do import with parameter BUFFER=209715200, it takes approximatively 4 hours & when we do without the parameter it takes 6 hours.

So i humbly request to above scenario of import process with buffer parameter in concern.

Also to expose, import is done in freeze hours.

View 2 Replies View Related

Server Utilities :: Exporting Schema Using Consistent Parameter

Aug 5, 2012

I taking export using consistent parameter. Theoretically i can understand . practically i couldn't understand how it works.

for ex

I am updating tab1 table under sams user. table having one lakh records.
while updating the query using consistent=y and consistent=n. i mean

exp sams/sams file=cons.dmp owner=sams consistent=y
exp sams/sams file=cons2.dmp owner=sams consistent=n

then both files imported to separate user(sam ,san).
Updated info not visible in san and sam user.

I want to know practically how it works. I need perfect example. while using consistent=y and consistent=n

View 2 Replies View Related

Server Utilities :: ROWS Command Line Parameter

Jan 11, 2013

I am new to SQL*loader and I would like to know what is the maximum number of ROWS that can be loaded in a Conventional bind array while specifying the command line parameter.

View 2 Replies View Related

Server Utilities :: Exporting Schema Using Filesize Parameter?

Aug 6, 2012

Export /Import
==============

While exporting schema's

i couldn't export dump file to exact location i mean see following query : -

QUERY
=====

exp file=ackupfile1.dmp,ackupfile2.dmp,ackupfile3.dmp
owner=(order,purchase) filesize=5m as os level ,

I fould those dump files files home directory.

-rw-r--r-- 1 oracle oinstall 5242880 Aug 6 19:38 expfile1.dmp
-rw-r--r-- 1 oracle oinstall 5242880 Aug 6 19:38 expfile2.dmp
-rw-r--r-- 1 oracle oinstall 5242880 Aug 6 19:38 expfile3.dmp
[oracle@localhost ~]$ pwd
/home/oracle

when i listing

rw-r--r-- 1 oracle oinstall 72 Jun 20 21:17 afiedt.buf
drwxr-xr-x 3 oracle oinstall 4096 Jun 17 10:07 Desktop
-rw-r--r-- 1 oracle oinstall 71 Jun 19 20:42 ed.hup
drwxr-xr-x 2 oracle oinstall 4096 Aug 6 19:38 backup
-rw-r--r-- 1 oracle oinstall 2826240 Aug 6 19:39 expdat.dmp
-rw-r--r-- 1 oracle oinstall 5242880 Aug 6 19:38 expfile1.dmp
-rw-r--r-- 1 oracle oinstall 5242880 Aug 6 19:38 expfile2.dmp
-rw-r--r-- 1 oracle oinstall 5242880 Aug 6 19:38 expfile3.dmp

Dump file goes to home path even if i mentioned appropriate location.

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved