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


ADVERTISEMENT

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 :: Export Illegal Parameter Value In OCI Lob Function?

Jan 21, 2011

We are trying to export our production data .We got this error

. . exporting table EA_BLOB
EXP-00056: ORACLE error 24801 encountered
ORA-24801: illegal parameter value in OCI lob function

how to overcome this error ?

View 2 Replies View Related

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 View Related

Server Utilities :: Data Pump Export With Query And Date Value?

Dec 24, 2010

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

[code]....

View 4 Replies View Related

Server Utilities :: Export Dump Of Large Table

Apr 9, 2010

We have two databases running on 10.2.0.4 and 9.2.0.8. Both are having the same unpartitioned table of size 80G. I am exporting the table on 10g by using parallel=8 and dumpfile with %U option. That took around 4 hours to export the table.

And on 9.2.0.8, i am exporting using below parameters, taking around 5 hours.

buffer=2000000
recordlength=64000

options i can try to speed up the export in both versions.

View 2 Replies View Related

Server Utilities :: Export Table In Multiple Files?

Jun 18, 2013

exporting a big table (many rows = 3.000.000). Using the command exp the error message returned is "expdat.dmp > EXP-00028: failed to open expdat.dmp for write". Is there a possibility to export this table in multiple files (as a splitter)?

View 10 Replies View Related

Server Utilities :: Oracle Table Export Hangs

Nov 29, 2010

I got a problem while exporting schema. Export hangs on a particular table table. i checked the table structure and integrity

(ANALYZE TABLE user.table_name VALIDATE STRUCTURE CASCADE; )

and it seems all okay.

View 5 Replies View Related

Server Utilities :: Get Total Time When Using Export A Table?

Jul 12, 2011

How can i get the total time when using export a table?

for example:

exp userid=test/test file=d: est.dmp tables=(tb_test)

View 4 Replies View Related

Server Utilities :: How To Export One Table To Few Separated Dump Files

Mar 24, 2010

I have a table with 9 regions. How can I export them in 9 seperated dump file?

View 4 Replies View Related

Server Utilities :: Import Only One Or Two Table From A Schema Export File

May 6, 2012

is it possible to import only one or two table from a schema export file or from a full database export file.

View 2 Replies View Related

Server Utilities :: How To Export Table Structure As Script In All Schema

Jan 9, 2011

how can i export all table structure as script

note : i have multi schema's not one schema

i use

SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name)
FROM DBA_TABLES u;

but i need it for all schemas

View 3 Replies View Related

Server Utilities :: Export Excel Sheet Data In Database Table?

Jul 17, 2012

i want to export excel sheet in database table, so i have converted excel file in .csv file(comma delimated)and made control file, then i started sqlldr by double clicking on it. path is-D:oracleproduct10.2.0client_1BIN

i run this command from cmd-

Microsoft Windows [Version 6.1.7600]
Copyright (c) 2009 Microsoft Corporation. All rights reserved.
C:UsersNeetesh>sqlldr scott/tiger@localdb control=c:/users/neetesh/scott_data.
ctl
SQL*Loader: Release 10.2.0.1.0 - Production on Tue Jul 17 17:20:33 2012

[code]....

and i attached the .ctl file. and .csv file is stored on same directory as .ctl file, why oracle couldn't find the .ctl file.

View 21 Replies View Related

Export/Import/SQL Loader :: Query Parameter And Partitions

Nov 28, 2012

Version : 11.2.0.3

EMP_DTL table is a subpartitioned table (Range partitioned by MonthID and subpartitioned by COUNTYR_CODE).

It has 40 million records. I just wanted to export 100,000 records altogeter from all partitions for testing purpose.

But when I ran the below expdp with QUERY , it was exporting 100,000 records from each subpartition of the table !!

expdp "'/ as sysdba'" tables = HRTB_CMBH.EMP_DTL dumpfile=EMP_DTL_BKP.dmp DIRECTORY= DATA_PMP1 QUERY=HRTB_CMBH.EMP_DTL:"where rownum < 100001" LOGFILE= exp-partitionedTable.log The log

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "HRTB_CMBH"."EMP_DTL":"TCO_201205"."TCO_201205_IND"  38.48 MB  100000 rows
. . exported "HRTB_CMBH"."EMP_DTL":"TCO_201206"."TCO_201206_IND"  42.51 MB  100000 rows
. . exported "HRTB_CMBH"."EMP_DTL":"TCO_201205"."TCO_201205_HKG"  31.28 MB  100000 rows
. . exported "HRTB_CMBH"."EMP_DTL":"TCO_201206"."TCO_201206_HKG"  32.97 MB  100000 rows

[Code]....

This is not mentioned in the Utitilies document. Is this expected behaviour ?

View 2 Replies View Related

SQL & PL/SQL :: Retrieve Subset Of Data From Table

Jan 20, 2012

How to retrieve first hundred records from a table ?

FYI
---

The table size is 5 GB
The table count is 127922653
Table has 14 columns

Table is partitioned as well.

The table has 10 partitions.

View 17 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

Export/Import/SQL Loader :: Export Consistent Parameter

Feb 24, 2013

When we set Consistent parameter as yes during export, we get a consistent dump of the database/ schema from the point it was taken by setting the transaction Read only.

So let say we are exporting a table TAB_1 and at the same time a different user updates one of the row of this table and then another user updated this row again so and so forth. So from where do export gets the image of this row which was present at the point in time when the export was initiated in the first place. Is it the Undo?

View 3 Replies View Related

Server Utilities :: Export Schemas From 10g To 11g?

Apr 27, 2011

I am trying to export schemas from 10g to 11g. The NLS_CHARACTERSET for 10g is WE8ISO8859P1 and the NLS_CHARACTERSET for 11g is WE8MSWIN1252. Is it fine or do I need to change the character set, so that I will be able to successfully do the export/import?

View -1 Replies View Related

Server Utilities :: Export Error?

Nov 28, 2007

i am having a problem when trying to export my DB,i could run an import fine,i have ran the catalog.sql,catproc.sql,catexp.sql and utlrp.sql again.Is it because the client and DB are different?How can i solve this problem

exp usr/pass file=exp_full.dmp log=exp_full.log full=y consistent=y
Export: Release 8.1.7.0.0 - Production on Wed Nov 28 13:40:04 2007
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Connected to: Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
With the Partitioning option

[code]...

View 14 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 :: Export / Import Errors?

Apr 26, 2011

I was asked to do export/import of some schemas from 10g(linux) to 11g(AIX) using original expor/import method. I did not consider the character set and started doing export and import. while exporting, I get questionable statistics error in export log file. In the import log, I see the error like CREATE DATABASE LINK "xxxxxxxxxxxxx" CONNECT TO "xxxx" IDENTIFIED BY...

What can be done with these errors?

View 4 Replies View Related

Server Utilities :: Export Data From Excel To 10g?

Mar 4, 2010

i need to export master data in excel sheets to our database and we use toad too. How i can export the data with the use of macros in excel. how i can export data from excel to oracle.

View 6 Replies View Related

Server Utilities :: Import / Export Log File

May 11, 2010

Whats is the usage of log file in Import/export .If i use following command ,it exports successfully

exp scott/tiger file=check.dmp log=empc.log tables=emp

and if i remove .log from here it will also export successfully So why do we use .log in import/export.

View 4 Replies View Related

Server Utilities :: How To Monitor Export And Import Job

Apr 28, 2012

how can i monitor the export and import job and how increase the export and import job performance.

can i monitor the export and import job by checking the log and dump file created by export and import and can its performance increase by configure parallism. m i right or not?

View 2 Replies View Related

Server Utilities :: Conflicting In Parameters Of Export

Sep 18, 2010

I had specified the below:

Q1: Can we combine the 2 parameters together (owner and tables)? If not, then what is the way to specify it....

Exp scott/tiger owner=scott tables=(T1)
Error msg is: conflicting modes specified.

Q2: what is the privilege need for exporting other schema's tables?

Q3: what is the use of export table with index and many, but without ROWS?

View 1 Replies View Related

Server Utilities :: Export Multiple Tables Only?

Jul 10, 2012

send me the command for exporting multiple tables(1000+) in Linux env. 9i db, i know we can do using spool command but dont know exactly how to put it. i know using Datapump but this is 9i.

View 7 Replies View Related







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