Helena Markova, April 10, 2001 - 3:59 am UTC
Vijay, April 10, 2001 - 7:27 pm UTC
Export Query Option
A reader, April 11, 2001 - 3:49 pm UTC
As always, Tom answered my question and helped me to find out a way that Oracle Expert used.
Best Regards Tom
Chuck, April 11, 2001 - 8:12 pm UTC
Branka
Branka Todorovic, January 08, 2002 - 10:50 am UTC
Can I join the table, here?
Prince, October 10, 2002 - 8:26 pm UTC
Tom,
I want to extract (export) the data from a child table, only knowing the parent table's where clause and then joing with the child table.
Ex,
CREATE TABLE P (ID number(8) primary key,
TSTAMP date
);
create table c (id number(8),
id2 number(8),
constraint fk_c foreign key (id) references p) ;
I want to export the data for the following query. (dump all the records from c, where its parent's tstampo is more than two months old)
select c.* from c, p where p.id = c.id and p.tstamp < add_months(sysdate, -2)
Can I do it using exp? What would be the other options?
Thank You!
October 11, 2002 - 8:07 pm UTC
the where clause would be:
where id in ( select id from p where tstamp < add_months(sysdate-2) )
That, when applied against C, gives you the rows you want.
Please ignore the prev posting. I got it working
Prince, October 10, 2002 - 8:36 pm UTC
Tom,
Please ignore the previous posting. I got it working now.
export..
vj, May 06, 2003 - 2:27 am UTC
the export with query...
1. first of all can i do 3 tables with 3 different queries in a single export..
guess no...if no,
then i wanted to have a single dmp file for these 3..
in the sense, is there any way to append into the dmp file..otherwise i have a problem of sending too many dump files as quite a few tables requires the query option while export..
rgds
May 06, 2003 - 7:50 am UTC
there is no syntax to support three different where clauses and somehow associate them with the right tables -- so no. you would need three exp commands to separate dmp files.
there are tools like
$ tar cf - f1.dmp f2.dmp f3.dmp | gzip > all.tar.gz
in unixland and I'm sure you could find something like 'zip' in windows if that is your platform to solve the multi-file issue as well as descrease considerably the size of the files you need to send.
Complex queries
Anand, March 22, 2004 - 12:43 pm UTC
I have this query that I need to use to load data in to a remote database. I use sql "COPY" command to accomplish this. Can this query be used inside an export "query" claus ??
SELECT /*+ PARALLEL(tls,6) PARALLEL(TL,6)*/
tl.* -
FROM test_limit_set tls, test_limit tl
WHERE tls.test_limit_set_sk = tl.test_limit_set_sk
AND mfg_area_name='TEST14'
AND NOT EXISTS
(select * from test_limit@saph.jove a
where tl.test_limit_sk = a.test_limit_sk)
AND NOT EXISTS
(select * from test_limit@saph.jove a
where tl.test_number = a.test_number
and tl.test_limit_set_sk = a.test_limit_set_sk) ;
This is the my parfile :
userid=saphdba/******
full=n
file=exp_tl.dmp
log=exp_tl.log
buffer=8192000
tables=sapphire.test_limit, sapphire.test_limit_set
query=" WHERE tls.test_limit_set_sk in (select test_limit_set_sk from test_limit_set where tls.mfg_area_name='TEST14')
AND NOT EXISTS
(select * from test_limit@saph.jove a
where tl.test_limit_sk = a.test_limit_sk)
AND NOT EXISTS
(select * from test_limit@saph.jove a
where tl.test_number = a.test_number
and tl.test_limit_set_sk = a.test_limit_set_sk)"
It is erroring out with :
About to export specified tables via Conventional Path ...
Current user changed to SAPPHIRE
. . exporting table TEST_LIMIT
EXP-00056: ORACLE error 904 encountered
ORA-00904: invalid column name
. . exporting table TEST_LIMIT_SET
EXP-00056: ORACLE error 904 encountered
ORA-00904: invalid column name
Export terminated successfully with warnings.
Your help is appreciated...
March 22, 2004 - 3:18 pm UTC
no, because the where clause is applied to a SINGLE table.
you would have to use two exports, each with a where clause that applied to a single table (eg: instead of joining, you would use IN( subquery ))
Thanks for the reply...
Anand, March 22, 2004 - 4:29 pm UTC
I agree. We are in RBO and using "IN" in RBO murders the performance!!
March 22, 2004 - 4:46 pm UTC
CTAS and export the table.....
Nikhil, March 07, 2005 - 6:45 pm UTC
i found this very useful and relevant to what i was looking for
problem with query clause
Graham Bridgey, June 20, 2005 - 5:22 am UTC
Tom,
Following your advice I've used a parfile for my export but I'm still getting errors when using a query clause. Am I missing something really obvious here?
here's the parfile contents
file=/export/home/oracle/GB_STATEMENT_NOPART_UPTO_.dmp rows=y
tables=(GB_STATEMENT_NOPART)
constraints=n
feedback=10000
filesize=99999744
indexes=y
log=GB_STATEMENT_NOPART_UPTO_.log
query="FROMDATE < round(last_day(add_months(sysdate,-12)))+1"
I get the following error when I attempt to run it...
About to export specified tables via Conventional Path ...
. . exporting table GB_STATEMENT_NOPART
EXP-00056: ORACLE error 933 encountered
ORA-00933: SQL command not properly ended
Export terminated successfully with warnings.
I've tried running it without the query clause and it's ok.
Thanks in advance for your help.
Graham
Please ignore the previous post
Graham Bridgey, June 20, 2005 - 9:06 am UTC
Tom,
sorry to have bothered you, I found my 'deliberate' mistake. I missed the WHERE keyword in the query clause.
Thanks
Graham
export using QUERY
Wendy, August 08, 2005 - 5:07 am UTC
Outstanding. If it wasn't for the guidance provided in this article, I'd still be struggling with escape characters and what not, in the Query clause.
Your par file idea is just Genius!
Thank you
selective data what are other ways ?
Parag Jayant Patankar, June 21, 2006 - 8:38 am UTC
Hi Tom,
We want a data from our production server, to our DWH server depending upon business rule. I can think of following practial ways to download "SELECTIVE DATA" from 50 tables ( Data in most of the tables is getting changed quite frequently )
1. exp with query clause
2. Your Pro*C program to download data into ASCII dump
3. FGAC / VPD and use exp with new user
Is there any other way to download "SELECTIVE DATA" ? According to me VPD is best option. What do you think ?
Kindly suggest.
thanks & regards
pjp
June 22, 2006 - 10:38 am UTC
4) dblinks (insert into select from)
5) sqlplus copy command
6) data pump
I like 4, 6, 1 - probably in that order myself.
Chetan, October 24, 2006 - 8:29 am UTC
Hi Tom,
Great Stuff... This has solved my issue instantly.Using parfile was something i never thought of using.
Export from info from 2 tables to be input into 1 table
Eunice, September 19, 2007 - 1:52 am UTC
Hi Tom,
I have tried the following but it's not working. Any idea if I can select from 2 tables to input into one dump export file? The 2 tables, non_member and member, have similar fields.
Thank you!
exp userid=production/production123 file=$expordchargefile log=$logchargefile GRANTS=N tables=charge query=\"where \( \(charge.TRANSACTION_ID\=non_member.TRANSACTION_ID and non_member.TIMESTAMP \< to_date\(\'18\-09\-07 00\:00\:00\', \'DD\-MM\-YY HH24\:MI\:SS\'\) and non_member.TIMESTAMP \> to_date\(\'15\-08\-07 00\:00\:00\', \'DD\-MM\-YY HH24\:MI\:SS\'\) \) or \(charge.TRANSACTION_ID\=member.TRANSACTION_ID and member.TIMESTAMP \< to_date\(\'18\-09\-07 00\:00\:00\', \'DD\-MM\-YY HH24\:MI\:SS\'\) and member.TIMESTAMP \> to_date\(\'15\-08\-07 00\:00\:00\', \'DD\-MM\-YY HH24\:MI\:SS\') \) \)\"
September 19, 2007 - 12:26 pm UTC
well, it is sort of an "abuse" of the query parameter, but....
ops$tkyte%ORA9IR2> create table t1 as select * from all_users;
Table created.
ops$tkyte%ORA9IR2> create table t2 as select * from all_users;
Table created.
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> select count(*) from t1;
COUNT(*)
----------
30
ops$tkyte%ORA9IR2> select count(*) from t2;
COUNT(*)
----------
30
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> !cat test.par
tables=t1<b>
query=" union all select * from t2"
</b>
ops$tkyte%ORA9IR2> !exp userid=/ parfile=test.par
Export: Release 9.2.0.8.0 - Production on Wed Sep 19 12:12:03 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
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 T1 60 rows exported
Export terminated successfully without warnings.
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> truncate table t1;
Table truncated.
ops$tkyte%ORA9IR2> !imp userid=/ full=y ignore=y
Import: Release 9.2.0.8.0 - Production on Wed Sep 19 12:12:03 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
Export file created by EXPORT:V09.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. importing OPS$TKYTE's objects into OPS$TKYTE
. . importing table "T1" 60 rows imported
Import terminated successfully without warnings.
ops$tkyte%ORA9IR2> select count(*) from t1;
COUNT(*)
----------
60
Thanks!
Eunice, September 24, 2007 - 10:28 pm UTC
Hi Tom!
Thanks for your help!
Export Error
Prasanna, January 02, 2008 - 12:00 pm UTC
I am trying to export a subset of data from a partitioned table based on a column condition.
The problem is am facing is that I am unable to get the export to work in case of a partitioned table whereas if it
is a non-partitioned table, the export works fine.
Here is my parfile:
___________________________________________________________________
file=expdci.dmp
query="where calendar_dt = to_date('07/01/2007','mm/dd/yyyy')"
tables=(DAILY_CASH_INVEST)
log=dci.log
userid=slddeveloper/slddeveloper@dev.newsld
___________________________________________________________________
Error:
___________________________________________________________________
C:\vaidy\SSGA-MCH\CASH_Detail_Reporting>exp parfile=expdci.par
Export: Release 8.1.7.1.1 - Production on Wed Jan 2 11:48:08 2008
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
Export done in WE8ISO8859P1 character set and UTF8 NCHAR character set
server uses UTF8 character set (possible charset conversion)
About to export specified tables via Conventional Path ...
EXP-00011: SLDDEVELOPER.DAILY_CASH_INVEST does not exist
Export terminated successfully with warnings.
___________________________________________________________________
I can vouch for the fact that this table exist in the schema.
What I also noticed is that if the same table was a non-partitioned table, then the export is successful.
I am unsure if I am missing something...
Is there a limitation on condition based export of partitioned table?
FYI...We are in 9.2.0.8.
January 02, 2008 - 3:36 pm UTC
I cannot reproduce, so I can only presume some sort of possible operator error?
In light of the fact we do not have a full example showing
a) table nonpartitioned works
b) table partitioned does not work
that is all I can guess. cut and paste a FULL example like I do....
ops$tkyte@ORA920> drop TABLE daily_cash_invest;
Table dropped.
ops$tkyte@ORA920>
ops$tkyte@ORA920> CREATE TABLE daily_cash_invest
2 (
3 calendar_dt date,
4 x int,
5 y int
6 )
7 PARTITION BY RANGE (calendar_dt)
8 (
9 PARTITION part1 VALUES LESS THAN (to_date('13-mar-2003','dd-mon-yyyy')),
10 PARTITION part2 VALUES LESS THAN (to_date('14-mar-2003','dd-mon-yyyy')),
11 PARTITION junk VALUES LESS THAN (MAXVALUE)
12 )
13 /
Table created.
ops$tkyte@ORA920> insert into daily_cash_invest
2 select to_date( '07/01/2007','mm/dd/yyyy'), rownum, rownum from all_users;
43 rows created.
ops$tkyte@ORA920> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production
Wed Jan 2 15:59:17 EST 2008
(tkyte@aria-dev) /export/home/tkyte
> dev817
Wed Jan 2 15:59:43 EST 2008
(tkyte@aria-dev) /export/home/tkyte
> cat exp.par
file=expdci.dmp
query="where calendar_dt = to_date('07/01/2007','mm/dd/yyyy')"
tables=(DAILY_CASH_INVEST)
log=dci.log
userid=ops$tkyte/foobar@ora920
Wed Jan 2 15:59:43 EST 2008
(tkyte@aria-dev) /export/home/tkyte
> exp parfile=exp.par
Export: Release 8.1.7.4.0 - Production on Wed Jan 2 15:59:53 2008
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production
Export done in US7ASCII character set and UTF8 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
About to export specified tables via Conventional Path ...
. . exporting table DAILY_CASH_INVEST
. . exporting partition PART1 0 rows exported
. . exporting partition PART2 0 rows exported
. . exporting partition JUNK 43 rows exported
Export terminated successfully without warnings.
export
Prasanna, January 07, 2008 - 5:01 pm UTC
Thanks Tom.
I got it corrected.
My script worked for partitioned tables as well.
The problem was due to the fact that I was using an 8i compatible export utility.
When I used 9i version of exp utility, I was able to do the same.
January 07, 2008 - 5:36 pm UTC
well...
I used 8i as well - I replicated your environment...
Export version
A reader, January 08, 2008 - 5:23 am UTC
.. may be exp 8.1.7.1.1 was not as intelligent as 8.1.7.4.0
export
prasanna, January 08, 2008 - 11:26 am UTC
I used 8.1.7.1.1 earlier when it failed.
When I used 9.2.0.1.0, it went through.
Not sure if there is some compatibility issues between 8.1.7.1.1 and 8.1.7.4.0 and 9i version.
Anyway, its all set.
Thanks again.
sintax
A reader, March 31, 2008 - 1:43 am UTC
which is the sintanx needed,on unix system, when colum name to export begins whith special caracter $ ?
example QUERY="WHERE $COL='something' "
thanks
March 31, 2008 - 9:28 am UTC
use a parameter file
eg: put the stuff in a file
exp userid=.... parfile=thatfile.txt
or read up on how to escape things in your shell scripting language of choice. Usually it is "\"
OK
A reader, June 04, 2008 - 8:21 am UTC
Hi Tom,
Since "query" parameter is valid only for Table mode exports,I would like to have a similar such parameter for schema level export. Is there any workaround for this?
For example in my requirement, all tables are partitioned by region and cannot be listed since they are numerous. So I would like to do an export for all tables for a region only like
"exp userid=/ schema=user1 query="""where region='ASIA'"""
June 04, 2008 - 10:20 am UTC
....
all tables are partitioned by region and cannot
be listed since they are numerous.
....
there you are wrong. just use a parameter file, use sqlplus to create a parameter file with a list of your table names and add to that whatever else you want and exp parfile=filename.whatever
use of Union in export query
Jigish Thakkar, January 12, 2009 - 10:13 am UTC
hi Tom,
I have a similar problem. Can we use the below query in the Export? (Using UNIONs). Will it work fine? We are using 9.2.0.1.0
WHERE Number in
\( SELECT number FROM t1 WHERE EXISTS \(SELECT T.col1 FROM TEMPLATE T WHERE T.col2=t1.col2\)
UNION SELECT number FROM t2 WHERE EXISTS \(SELECT T.col1 FROM TEMPLATE T WHERE T.col2=t2.col2\)
UNION SELECT number FROM t3 WHERE EXISTS \(SELECT T.col1 FROM TEMPLATE T WHERE T.col2=t3.col2\)
UNION SELECT number FROM t4
\)
January 12, 2009 - 9:17 pm UTC
is it a valid where clause?
if so, yes, it'll work
if no, no, it'll not work
Nishi, January 14, 2009 - 6:50 am UTC
I tried to check exp help=y in unix, but cannot see query option there. What can be the alternative to fetch rows based on certain condition?
January 16, 2009 - 4:26 pm UTC
well, since query= has been there since at least 8i, you'd need to first say "what version" you are using
Querys for Exporting Oracle Dumps
KANAYO DANIEL, January 28, 2009 - 9:04 am UTC
PLEASE I WANT A QUERY FOR EXPORT DUMP IN ORACLE
January 28, 2009 - 3:45 pm UTC
PLEASE, I WANT $100
oh wait, just hit the capslock key....
I showed you how to use query= with export, can you be a tad more detailed about what you want to do? It is not at all clear.
EXPORT with Query parameter
Ramya, April 20, 2009 - 9:30 pm UTC
Hi,
I am trying to export a table with the query parameter.
But I have problem with the column name of the table.
The column name has to be specified inside double quotes in the query, otherwise it wont be recognised.
So the query parameter will look like:
QUERY=IM."IM_LoginHistory":"where "EventTime" > (sysdate - 2)"
But Export will consider those double quotes as bad syntax and returns error.
Can you please suggest a work around for this please..
Thanks in advance,
Ramya
April 21, 2009 - 2:56 pm UTC
Export is not considering them - your SHELL IS.
do you know how to escape things in your shell?
ops$tkyte%ORA10GR2> create table t ( "mixed case" number );
Table created.
ops$tkyte%ORA10GR2> insert into t values ( 0 );
1 row created.
ops$tkyte%ORA10GR2> insert into t values ( 1 );
1 row created.
ops$tkyte%ORA10GR2> commit;
Commit complete.
ops$tkyte%ORA10GR2> !exp userid=/ tables=t query=\"where \"\"mixed case\"\" = 0\"
Export: Release 10.2.0.1.0 - Production on Tue Apr 21 14:51:02 2009
Copyright (c) 1982, 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
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 T 1 rows exported
Export terminated successfully without warnings.
ops$tkyte%ORA10GR2> delete from t;
2 rows deleted.
ops$tkyte%ORA10GR2> commit;
Commit complete.
ops$tkyte%ORA10GR2> !imp userid=/ full=y ignore=y
Import: Release 10.2.0.1.0 - Production on Tue Apr 21 14:51:22 2009
Copyright (c) 1982, 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
Export file created by EXPORT:V10.02.01 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. importing OPS$TKYTE's objects into OPS$TKYTE
. importing OPS$TKYTE's objects into OPS$TKYTE
. . importing table "T" 1 rows imported
Import terminated successfully without warnings.
ops$tkyte%ORA10GR2> select * from t;
mixed case
----------
0
Ramya, April 21, 2009 - 8:05 pm UTC
I tried escaping and also tried with parameter file. But it till gives me error.
expdp un/pswd DIRECTORY=dpump_dir1 DUMPFILE=IMTABLES_LH3.dmp CONTENT=DATA_ONLY TABLES='IM."IM_LoginHistory"' QUERY=\"where \"\"EventTime\"\" \> \(sysdate-2\)\"
ORA-31693: Table data object "IM"."IM_LoginHistory" failed to load/unload and is being skipped due to error:
ORA-06502: PL/SQL: numeric or value error
ORA-31605: the following was returned from LpxXSLSetTextVar in routine kuxslSetParam:
LPX-314: an internal failure occurred
If I remove any of the baclslash from the command it throws an syntax error. And also for the Tablename it works only if I enter tablename in that format.
I finally gave up on this one and created a view matching that query for the table. But now I am not able to export a view using expdp.
Can you please suggest me, if there is a way to export either that table or even exporting a view would help me a lot.
Thanks in advance,
Ramya
April 22, 2009 - 2:13 pm UTC
it would be really cool to know, well, your OS wouldn't it??? And what shell you are using???
Ramya, April 21, 2009 - 8:40 pm UTC
When i try to export view i get the following error.
ORA-39165: Schema IM was not found.
ORA-39168: Object path VIEW was not found.
ORA-31655: no data or metadata objects selected for job
The command i use is:
expdp un/pswd DIRECTORY=Dpump_IMdir1 DUMPFILE=IMTABLES_HWM.dmp CONTENT=ALL INCLUDE=VIEW\:\"IN\(\'View_PD_ShoppingCart\'\,\'View_DloadReport_HWM\'\)\" SCHEMAS=IM
even without backslash i get the same error.
Ramya, April 21, 2009 - 10:14 pm UTC
Sorry, Forgot to mention that I am running this export on linux machine.
A reader, April 23, 2009 - 1:02 am UTC
Hi,
I did mention the OS but posted it later as a different post. Sorry for that.
I am running it on linux and am using BASH shell.
Regards,
Ramya
April 27, 2009 - 10:28 am UTC
your table names are in mixed case right? otherwise, identifiers are UPPERCASE.
if I execute:
create or replace view im."View_PD_ShoppingCart" as select * from dual;
create or replace view im."View_DloadReport_HWM" as select * from dual;
then
$ expdp im/im content=all include=view\:\"in\(\'View_PD_ShoppingCart\',\'View_DloadReport_HWM\'\)\" schemas=im
Export: Release 10.2.0.4.0 - Production on Monday, 27 April, 2009 10:11:42
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "IM"."SYS_EXPORT_SCHEMA_01": im/******** content=all include=view:"in('View_PD_ShoppingCart','View_DloadReport_HWM')" schemas=im
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Master table "IM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for IM.SYS_EXPORT_SCHEMA_01 is:
/tmp/expdat.dmp
Job "IM"."SYS_EXPORT_SCHEMA_01" successfully completed at 10:11:53
Export with complex query
Tariq, May 28, 2010 - 6:10 pm UTC
Dear Tom,
I am trying to make a exp in oracle 9.2.0.7 with the below query but facing problem . please assist.
--Query--
SELECT I.SUBSCR_TYPE, I.SUBNO, I.STATUS, I.CONTRNO, DECODE(I.PREPOST_PAID, 'PREP', 'HYBD', I.PREPOST_PAID) SERVED_TYPE,
I.TARIFF_PROFILE, I.SUBS_RANKING, C.IMSI_NUMBER, C.CONDATE
FROM CRM_USER_INFO I, CRM_COMMDEVICE C
WHERE (PREPOST_PAID='POST' OR EXISTS (SELECT EQ.SUBNO FROM CRM_USER_EQUIPMENTS EQ WHERE I.SUBNO=EQ.SUBNO AND EQ.EQUIPID LIKE 'VPR%' ) ) AND C.SUBNO(+)=I.SUBNO AND C.STATUS(+)=40 AND I.SUBNO LIKE '3%'
AND I.SUBSCR_TYPE='G'
--Exp--
exp system/xxxx file=abc.dmp log=abc.log tables=tabs.CRM_USER_INFO,tabs.CRM_COMMDEVICE query=" WHERE (PREPOST_PAID='POST' OR EXISTS (SELECT EQ.SUBNO FROM CRM_USER_EQUIPMENTS EQ WHERE I.SUBNO=EQ.SUBNO AND EQ.EQUIPID LIKE 'VPR%' ) ) AND C.SUBNO(+)=I.SUBNO AND C.STATUS(+)=40 AND I.SUBNO LIKE '3%' AND I.SUBSCR_TYPE='G'"
Regards,
May 29, 2010 - 8:40 am UTC
my car won't start.
I used this key to start it <pretend there is a picture of a key here>
Why won't my car start?
we are even, you have as much information about my problem as I do yours. when you can answer me, I'll try to answer you.
How about a cut and paste of what *happens*, the statment:
"but facing problem"
is not even a tiny bit useful to anyone. Think about it - what would you need from your coworkers to help them diagnose an issue.
I don't know your error or symptoms
I don't know your platform (operating system)
I don't know very much about your problem.
export complex query
Tariq, May 29, 2010 - 9:03 am UTC
Hi tom,
we are using oracle 9.2.0.7 and OS Solaris 9.
I tried many times to execute the exp with some changes but every time i received to check help. Please check the examples I unsed and correct me and let me know exeact method of exp with the given query.
We are receiving the below errors
tabs @mtcvbdev:/ccbtest/tariq>exp system/xxxx file=abc.dmp log=abc.log tables=tabs.CRM_USER_INFO,tabs.CRM_COMMDEVICE query=" WHERE (PREPOST_PAID='POST' OR EXISTS (SELECT EQ.SUBNO FROM CRM_USER_EQUIPMENTS EQ WHERE I.SUBNO=EQ.SUBNO AND EQ.EQUIPID LIKE 'VPR%' ) ) AND C.SUBNO(+)=I.SUBNO AND C.STATUS(+)=40 AND I.SUBNO LIKE '3%'
> AND I.SUBSCR_TYPE='G'"
LRM-00116: syntax error at 'PREPOST_PAID' following '('
EXP-00019: failed to process parameters, type 'EXP HELP=Y' for help
EXP-00000: Export terminated unsuccessfully
tabs @mtcvbdev:/ccbtest/tariq>
and also we tried like the below examples: but still issues
tabs @mtcvbdev:/ccbtest/tariq>exp system/xxxx file=abc.dmp log=abc.log tables=tabs.CRM_USER_INFO,tabs.CRM_COMMDEVICE query=" WHERE (PREPOST_PAID='POST' OR EXISTS (SELECT EQ.SUBNO FROM CRM_USER_EQUIPMENTS EQ WHERE I.SUBNO=EQ.SUBNO AND EQ.EQUIPID LIKE 'VPR%' ) )"
LRM-00116: syntax error at 'PREPOST_PAID' following '('
EXP-00019: failed to process parameters, type 'EXP HELP=Y' for help
EXP-00000: Export terminated unsuccessfully
tabs @mtcvbdev:/ccbtest/tariq>exp system/xxxx file=abc.dmp log=abc.log tables=tabs.CRM_USER_INFO,tabs.CRM_COMMDEVICE query=" WHERE PREPOST_PAID='POST' OR EXISTS (SELECT EQ.SUBNO FROM CRM_USER_EQUIPMENTS EQ WHERE I.SUBNO=EQ.SUBNO AND EQ.EQUIPID LIKE 'VPR%')"
LRM-00116: syntax error at 'I.SUBNO' following 'WHERE'
EXP-00019: failed to process parameters, type 'EXP HELP=Y' for help
EXP-00000: Export terminated unsuccessfully
tabs @mtcvbdev:/ccbtest/tariq>exp system/xxxx file=abc.dmp log=abc.log tables=tabs.CRM_USER_INFO,tabs.CRM_COMMDEVICE query=" WHERE PREPOST_PAID='POST' OR EXISTS (SELECT EQ.SUBNO FROM CRM_USER_EQUIPMENTS EQ WHERE I.SUBNO=EQ.SUBNO AND EQ.EQUIPID LIKE 'VPR%') AND C.SUBNO(+)=I.SUBNO AND C.STATUS(+)=40 AND I.SUBNO LIKE '3%' AND I.SUBSCR_TYPE='G'"
LRM-00116: syntax error at 'I.SUBNO' following 'WHERE'
EXP-00019: failed to process parameters, type 'EXP HELP=Y' for help
EXP-00000: Export terminated unsuccessfully
REgards,
May 29, 2010 - 10:36 am UTC
I always suggest using a parameter file - to avoid any possible confusion with how to escape special shell characters - which is exactly what you are running into.
Hence, your existing problem really isn't a database question, it is a shell scripting issue - the shell treats (, ', ), ", etc "special" and you are not escaping your string properly.
Here is an excerpt from a book I wrote:
<quote>
Subsetting DataOracle8i introduced the ability for EXP to export only selected rows from a table. Prior to this release, EXP was an all or nothing event; either every row was exported or no rows were. We can now use the QUERY= parameter to supply a where clause that will be applied to each table exported. It should be noted that when using a where clause, QUERY and the DIRECT mode of export is not allowed; if you want a subset of data, you will be using the conventional path mode of EXP.
The method by which you specify the QUERY= parameter depends on the operating system. A where clause generally has many special characters in it such as =, > ,<, and spaces. The shell command prompt in Unix and Windows is not going to like those characters very much. They will have to be escaped and how to do that is OS-dependent. What I prefer to do is to always use a PARFILE with the QUERY option. In this way, I can use the same exact methods regardless of platform.
As an example of this, I have created a table T as select * from all_objects. I want to export all rows such that the object_id is less then 5000. On Windows I would have to execute:
C:\exp>exp userid=tkyte/tkyte tables=t query="""where object_id < 5000"""
Note that we need three double quotes on each side of the where clause in Windows. Now on UNIX I must use:
$ exp userid=/ tables=t query=\"where object_id \< 5000\"
But, if I simply use a parameter file with this in it:
query="where object_id < 5000"
I can now use the single command:
exp userid=/ tables=t parfile=exp.par
on both systems without change. I find this to be much easier than trying to properly escape the QUERY strings on each platform.
</quote>
Export with complex query
Tariq, May 29, 2010 - 2:01 pm UTC
Dear Tom,
Thanks very much for an excelent example of a parameter file.I have tried to take export on behalf of your example.
1- I made one parameter file (abc.par) below is the query in it.
--abc.par
query="WHERE (PREPOST_PAID='POST' OR EXISTS (SELECT EQ.SUBNO FROM CRM_USER_EQUIPMENTS EQ WHERE I.SUBNO=EQ.SUBNO AND EQ.EQUIPID LIKE 'VPR%' ) ) AND C.SUBNO(+)=I.SUBNO AND C.STATUS(+)=40 AND I.SUBNO LIKE '3%'AND I.SUBSCR_TYPE='G'"
2- here is the exp statment along with parameter file.
exp system/xxxxxxxxx file=abc.dmp log=abc.log tables=tabs.CRM_USER_INFO ,tabs.CRM_COMMDEVICE parfile=abc.par
Atleast this time exp started.... and received the below error message because of aliases of tables.
Export: Release 9.2.0.7.0 - Production on Sat May 29 21:49:53 2010
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production
Export done in AR8MSWIN1256 character set and UTF8 NCHAR character set
server uses AR8ISO8859P6 character set (possible charset conversion)
About to export specified tables via Conventional Path ...
Current user changed to TABS
. . exporting table CRM_USER_INFO
EXP-00056: ORACLE error 904 encountered
ORA-00904: "I"."SUBSCR_TYPE": invalid identifier
. . exporting table CRM_COMMDEVICE
EXP-00056: ORACLE error 904 encountered
ORA-00904: "I"."SUBSCR_TYPE": invalid identifier
Export terminated successfully with warnings.
I tried to give aliases in TABLES clause but getting the below errors.
exp system/xxxxxx file=abc.dmp log=abc.log tables=tabs.CRM_USER_INFO I,tabs.CRM_COMMDEVICE C parfile=abc.par
About to export specified tables via Conventional Path ...
Current user changed to TABS
. . exporting table CRM_USER_INFO
EXP-00056: ORACLE error 904 encountered
ORA-00904: "I"."SUBSCR_TYPE": invalid identifier
Current user changed to SYSTEM
EXP-00011: SYSTEM.I does not exist
Current user changed to TABS
. . exporting table CRM_COMMDEVICE
EXP-00056: ORACLE error 904 encountered
ORA-00904: "I"."SUBSCR_TYPE": invalid identifier
Current user changed to SYSTEM
EXP-00011: SYSTEM.C does not exist
Export terminated successfully with warnings.
HOw can I resove the table aliases w.r.t my query.
Regards,
May 30, 2010 - 6:55 am UTC
you cannot use table aliases
The query is against a single table, the table being exported.
It would seem you will need TWO exports, with TWO where clauses.
I'm not even really sure what you are doing - looking at your where clause.
WHERE
(PREPOST_PAID='POST'
OR
EXISTS (SELECT EQ.SUBNO
FROM CRM_USER_EQUIPMENTS EQ
WHERE I.SUBNO=EQ.SUBNO
AND EQ.EQUIPID LIKE 'VPR%' )
)
AND
C.SUBNO(+)=I.SUBNO
AND C.STATUS(+)=40
AND I.SUBNO LIKE '3%'
AND I.SUBSCR_TYPE='G'"
It looks like you think you can "join" the tables you are exporting - you cannot, it doesn't even begin to work like that. That where clause will be applied to EACH table in the tables list in turn - not at the "same time".
If you want to export a complex join, using EXP, you will
a) create table data_you_want as select
b) export tables=data_you_want
If you are using datapump, you can use an external table instead to unload any query.
Export with complex query
Tariq, May 31, 2010 - 9:03 am UTC
Dear Tom,
Thanks for your support.
We did with the below plan:
2- Create table as select query
3- Truncate table
4- Insert data from the select query
5- Export DMP
6- Schedule it
Is this ok.
Regards,
June 08, 2010 - 7:28 am UTC
why not
a) create table as select
b) drop it
c) rename (a)
d) skip 5 & 6 from above....
Problem with a column named "USER"
APH, October 27, 2010 - 4:10 pm UTC
I'm attempting to export a subset of table T using QUERY, like this:
CREATE TABLE T (
"USER" VARCHAR2(10)
);
INSERT INTO T ("USER") VALUES ('A');
INSERT INTO T ("USER") VALUES ('B');
INSERT INTO T ("USER") VALUES ('C');
COMMIT;
Then, in my parameter file I'd like to do something like this:
QUERY=T:"WHERE "USER" = 'A'"
Unfortunately, the double-quotes around USER are being misinterpreted (this is outside the OS as this is in a parameter file). I've tried escaping the double-quotes several ways, but no luck. And I must include quotes, otherwise the pseudocolumn USER (as in the logged in user's schema) is assumed and not the column USER on the table.
Is there a way to do this without renaming the column?
October 27, 2010 - 4:27 pm UTC
[tkyte@localhost ~]$ expdp / tables=t parfile=par
Export: Release 11.2.0.1.0 - Production on Wed Oct 27 17:26:34 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "OPS$TKYTE"."SYS_EXPORT_TABLE_01": /******** tables=t parfile=par
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
. . exported "OPS$TKYTE"."T" 5.015 KB 1 rows
Master table "OPS$TKYTE"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for OPS$TKYTE.SYS_EXPORT_TABLE_01 is:
/home/ora11gr2/app/ora11gr2/admin/orcl/dpdump/expdat.dmp
Job "OPS$TKYTE"."SYS_EXPORT_TABLE_01" successfully completed at 17:27:11
<b>
[tkyte@localhost ~]$ cat par
query=T:"where \"USER\"='A'"
</b>[tkyte@localhost ~]$
Prakash, February 10, 2011 - 8:21 am UTC
Dear Tom,
SQL> select count(1) from SGW.SG_MSG_FIX_OUT where market_id = 'Idem' and trunc(msg_date)='21-Jan-11';
COUNT(1)
----------
5186
I need to export the above table satifying the above where condition.But while exporting using "QUERY" i am not getting the correct count of records.Please find the content of my parfile (Filename "tmpparfile") below.
file=/apps/oracle/backup/SGWWDD01/exp_SGWWDD01_SG_MSG_FIX_OUT.dmp log=/apps/oracle/backup/SGWWDD01/exp_SGWWDD01_SG_MSG_FIX_OUT.log QUERY="WHERE market_id='Ide
m' and msg_date = to_date('21/01/2011','DD/MM/YYYY')" statistics=none
I run the export command.please find the result of the export below.
exp userid=\"/ as sysdba\" tables=SGW.SG_MSG_FIX_OUT parfile=tmpparfile
Export: Release 10.2.0.4.0 - Production on Thu Feb 10 14:59:50 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in WE8ISO8859P1 character set and UTF8 NCHAR character set
server uses UTF8 character set (possible charset conversion)
About to export specified tables via Conventional Path ...
Current user changed to SGW
. . exporting table SG_MSG_FIX_OUT 592 rows exported
Export terminated successfully without warnings.
Please give your solution to export the correct count of records (5186) as mentioned in the above select statement.
February 10, 2011 - 5:02 pm UTC
your query used trunc(msg_date)
your parfile didn't
Prakash, February 11, 2011 - 6:14 am UTC
Dear Tom,
Thanks for your answer.It works fine now.
where clause limitation
Tony Fernandez, February 15, 2011 - 9:21 am UTC
Tom,
In many examples above we use the where clause to filter rows in the data pump extracting data. Do you know if there is a limitation as to the maximum number of characters we can use in the where clause or can it be any length?
Thanks,
February 15, 2011 - 9:46 am UTC
I've never hit the limit, I'm not aware if there is (an undocumented) one however, there is no documented limit
I just tried a 53k where clause - it worked.
Audit Vault(osuser_dim)
Moeti, March 17, 2011 - 4:14 am UTC
Hi Tom,
I am doing report from Audit Vault Database for auding purpose.
I need to select the os-user name from the table where AUDIT_EVENT_FACT.OSUSER_DIM column is referenced. I don not get the source table when this column is coming.
Thanks.
Best Regards
Moeti
export import 2TB table from DB to another,
A reader, March 17, 2011 - 9:52 am UTC
Hello,
We have a 28 Billion row table in a database and its approximate size is 2 TB. We need to copy the data to a table located in a different database (different server). This is partitioned table however, 99% of the data is sitting in just one partition (poor maintenance).
The following idea popped up for me. This uses the concept of splitting the table virtually by generating non-overlapping range of ROWIDs using dba_extents (using the query you have provided) and export each piece at a time. The export script will have "where" condition.
I am thinking of splitting this table into 1000 pieces (that way each piece is less than or equal to 2 GB) and export -> ftp the dmp file to the other server -> imp.
Another thought is to use INSERT..SELECT on each piece and copy it over the DB link.
Are there any better way to perform this action? Time is not a constraint but we need to easy on the server by not taking up all its resources and also if any error or issues arises, we need to be able to continue from that point onwards.
Thanks,
March 17, 2011 - 9:55 am UTC
I'm thinking "transport it"
You can do it right from your backup set, you'll just basically be restoring it to another database.
look up transportable tablespaces.
follow up,
A reader, March 17, 2011 - 12:45 pm UTC
Hello,
Thanks for your suggestion. It looks a very viable option. However, the tablespace on which this particular table resides contains lot of other tables. I don't want to migrate those tables. Can we ignore those? If not, I can transport all the objects in that tablespace and drop the unwanted later. What if the tablespace name already exists in target database?
Thanks,
March 17, 2011 - 12:47 pm UTC
If not, I can
transport all the objects in that tablespace and drop the unwanted later. What
if the tablespace name already exists in target database?
that is what you'd have to do - transport them all and drop them.
If the tablespace name already exists - you can
a) rename it
b) attach the new datafiles and bring the tablespaces online
c) rename that tablespace
d) rename (a) back
(c) and (d) are optional.
expdp with query not exporting rows
aliyar, April 29, 2011 - 12:50 pm UTC
Dear Tom ,
DB : 10.2.0.4.0
o/s : HP-Ux
i am running the following expdp. need to export the rows from hr.p_r_result object which are satisfying the condition of report_date>to_date(12/01/2010)...
expdp scott/***** directory=EXPDP_DIR query=HR.p_r_result:'"WHERE \"report_date\" > to_date ('12/01/2010')"' dumpfile=p_r_result.dmp logfile=p_r_result.log
Export: Release 10.2.0.4.0 - 64bit Production on Thursday, 28 April, 2011 14:10:37
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01": SCOTT/****** directory=EXPDP_DIR query=HR.p_r_result:"WHERE \"report_date\" > to_date (12/01/2010)" dumpfile=p_r_result.dmp logfile=p_r_result.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PASSWORD_HISTORY
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
but it is not exporting any rows ... there are around 300,000 rows which are satisfying the condition ..
tried dirfferent syntaxes for query option ( got it from google ) . but no luck ..
can you please correct me why expdp not exporting rows
Appreciate your help for DBAs
Thanks
Aliyar
April 29, 2011 - 1:03 pm UTC
well, that where clause isn't looking very good is it?
is the column name really in lowercase?
where is your date format for the to_date - is that 1-dec-2010 or 12-jan-2010?
I suggest you use a parameter file to avoid the messy escaping you have to do for the shell otherwise.
$ cat dp.par
query=ops$tkyte.p_r_result:"where report_date > to_date( '12/01/2010', 'mm/dd/yyyy' )"
$ expdp / parfile=dp.par tables=p_r_result
Export: Release 11.2.0.2.0 - Production on Fri Apr 29 14:01:53 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "OPS$TKYTE"."SYS_EXPORT_TABLE_01": /******** parfile=dp.par tables=p_r_result
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
. . exported "OPS$TKYTE"."P_R_RESULT" 5.031 KB 1 rows
Master table "OPS$TKYTE"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for OPS$TKYTE.SYS_EXPORT_TABLE_01 is:
/home/ora11gr2/app/ora11gr2/admin/ora11gr2/dpdump/expdat.dmp
Job "OPS$TKYTE"."SYS_EXPORT_TABLE_01" successfully completed at 14:02:04
expdp with query
aliyar, April 29, 2011 - 4:06 pm UTC
Thanks a lot Tom
your example worked finally .. learnt that whenever using query option we must use parfile to avoid any O/S shell related problems
Thanks
Aliyar
impdp fails with ORA-31603
aliyar, May 01, 2011 - 12:22 pm UTC
Dear Tom ,
Need to import a table all rows from prod into test
in prod table owner is : P
in test table owner is : T
Both databases are 10.2.0.4.0 and o/s : Hp-ux
first taken the export from prod successfully as follows
==================================================
expdp hr/hr directory=DPUMP_DIR7 dumpfile=P.dmp logfile=P.log tables=P.table1
==================================================
while importing in test with the following command , fails with error : ORA-31603
$:/oradb_export/EXP1>impdp system/***** parfile=dp.par
Import: Release 10.2.0.4.0 - 64bit Production on Saturday, 30 April, 2011 8:16:29
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Oracle Label Security, OLAP,
Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TABLE_01": system/****** parfile=dp.par
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39152: Table "T"."TABLE1" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
ORA-31693: Table data object "T"."TABLE1" failed to load/unload and is being skipped due to error:
ORA-31603: object "TABLE1" of type TABLE not found in schema "T"
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_TABLE_01" completed with 2 error(s) at 08:16:36
$:/oradb_export/EXP1>cat dp.par
directory=EXP_DIR dumpfile=P.dmp logfile=T.log tables=table1 remap_schema=P:T TABLE_EXISTS_ACTION=APPEND
$:/oradb_export/EXP1>
not sure why it is failing .. alos searched in metalink .. could ot find any note which is related to my issue
can you please help me point out where exactly i have made mistake
Thanks in advance
Aliyar
May 04, 2011 - 11:44 am UTC
it should work as demonstrated below. I'll have to refer you to support on this one unless you can give me a script to reproduce with (It might be some aspect of what table table1 "is", how it is created)
ops$tkyte%ORA10GR2> connect /
Connected.
ops$tkyte%ORA10GR2> drop user p cascade;
drop user p cascade
*
ERROR at line 1:
ORA-01918: user 'P' does not exist
ops$tkyte%ORA10GR2> drop user t cascade;
drop user t cascade
*
ERROR at line 1:
ORA-01918: user 'T' does not exist
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> grant dba to p identified by p;
Grant succeeded.
ops$tkyte%ORA10GR2> grant dba to t identified by t;
Grant succeeded.
ops$tkyte%ORA10GR2> pause
ops$tkyte%ORA10GR2> connect p/p
Connected.
p%ORA10GR2> create table table1 as select * from all_users;
Table created.
p%ORA10GR2> pause
p%ORA10GR2> connect t/t
Connected.
t%ORA10GR2> create table table1 as select * from all_users where 1=0;
Table created.
t%ORA10GR2> pause
t%ORA10GR2>
t%ORA10GR2> connect /
Connected.
ops$tkyte%ORA10GR2> !expdp / dumpfile=P.dmp logfile=P.log tables=P.table1
Export: Release 10.2.0.4.0 - Production on Wednesday, 04 May, 2011 12:42:51
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "OPS$TKYTE"."SYS_EXPORT_TABLE_01": /******** dumpfile=P.dmp logfile=P.log tables=P.table1
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
. . exported "P"."TABLE1" 7 KB 69 rows
Master table "OPS$TKYTE"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for OPS$TKYTE.SYS_EXPORT_TABLE_01 is:
/tmp/P.dmp
Job "OPS$TKYTE"."SYS_EXPORT_TABLE_01" successfully completed at 12:42:55
ops$tkyte%ORA10GR2> pause
ops$tkyte%ORA10GR2> !impdp system/manager dumpfile=P.dmp logfile=T.log tables=table1 remap_schema=P:T table_exists_action=append
Import: Release 10.2.0.4.0 - Production on Wednesday, 04 May, 2011 12:43:05
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TABLE_01": system/******** dumpfile=P.dmp logfile=T.log tables=table1 remap_schema=P:T table_exists_action=append
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39152: Table "T"."TABLE1" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "T"."TABLE1" 7 KB 69 rows
Job "SYSTEM"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 12:43:07
ops$tkyte%ORA10GR2> select count(*) from t.table1;
COUNT(*)
----------
69
selectively export objects
Ravi B, May 04, 2011 - 7:15 pm UTC
Hi Tom,
We currently do a full export/import for one of our databases.
We wanted to restrict the table data based on a date column.
Is it possible to :
1) export tables (t1,t2...tn) query = <query_where>
and
2) export all other objects
Thanks!
May 06, 2011 - 9:27 am UTC
by doing two exports - yes.
if you use datapump, it'll be easier, the filtering is much much more powerful with data pump.
export and import
suriya, May 12, 2011 - 1:53 am UTC
Hi tom,
let me know the Advantage of datapump export(expdm) and import(impdm) over original export(exp) and import(imp)
May 12, 2011 - 7:43 am UTC
EXPORT
ARUN KUMAR, May 11, 2013 - 8:34 am UTC
HOW TO EXPORT PARTICULAR TABLE FILES ! OR
HOW TO EXPORT PARTICULAR TABLE TO ANOTHER USER
May 12, 2013 - 3:43 pm UTC
READING DOCUMENTATION IS ALWAYS GOOD.
[tkyte@localhost ~]$ exp help=y
Export: Release 11.2.0.3.0 - Production on Sun May 12 11:41:38 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
You can let Export prompt you for parameters by entering the EXP
command followed by your username/password:
Example: EXP SCOTT/TIGER
Or, you can control how Export runs by entering the EXP command followed
by various arguments. To specify parameters, you use keywords:
Format: EXP KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
Example: EXP SCOTT/TIGER GRANTS=Y TABLES=(EMP,DEPT,MGR)
or TABLES=(T1:P1,T1:P2), if T1 is partitioned table
USERID must be the first parameter on the command line.
Keyword Description (Default) Keyword Description (Default)
--------------------------------------------------------------------------
USERID username/password FULL export entire file (N)
BUFFER size of data buffer OWNER list of owner usernames
FILE output files (EXPDAT.DMP) TABLES list of table names
COMPRESS import into one extent (Y) RECORDLENGTH length of IO record
GRANTS export grants (Y) INCTYPE incremental export type
INDEXES export indexes (Y) RECORD track incr. export (Y)
DIRECT direct path (N) TRIGGERS export triggers (Y)
LOG log file of screen output STATISTICS analyze objects (ESTIMATE)
ROWS export data rows (Y) PARFILE parameter filename
CONSISTENT cross-table consistency(N) CONSTRAINTS export constraints (Y)
OBJECT_CONSISTENT transaction set to read only during object export (N)
FEEDBACK display progress every x rows (0)
FILESIZE maximum size of each dump file
FLASHBACK_SCN SCN used to set session snapshot back to
FLASHBACK_TIME time used to get the SCN closest to the specified time
QUERY select clause used to export a subset of a table
RESUMABLE suspend when a space related error is encountered(N)
RESUMABLE_NAME text string used to identify resumable statement
RESUMABLE_TIMEOUT wait time for RESUMABLE
TTS_FULL_CHECK perform full or partial dependency check for TTS
VOLSIZE number of bytes to write to each tape volume
TABLESPACES list of tablespaces to export
TRANSPORT_TABLESPACE export transportable tablespace metadata (N)
TEMPLATE template name which invokes iAS mode export
Export terminated successfully without warnings.
read up on the TABLES parameter for exporting particular tables.
[tkyte@localhost ~]$ imp help=y
Import: Release 11.2.0.3.0 - Production on Sun May 12 11:42:25 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
You can let Import prompt you for parameters by entering the IMP
command followed by your username/password:
Example: IMP SCOTT/TIGER
Or, you can control how Import runs by entering the IMP command followed
by various arguments. To specify parameters, you use keywords:
Format: IMP KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
Example: IMP SCOTT/TIGER IGNORE=Y TABLES=(EMP,DEPT) FULL=N
or TABLES=(T1:P1,T1:P2), if T1 is partitioned table
USERID must be the first parameter on the command line.
Keyword Description (Default) Keyword Description (Default)
--------------------------------------------------------------------------
USERID username/password FULL import entire file (N)
BUFFER size of data buffer FROMUSER list of owner usernames
FILE input files (EXPDAT.DMP) TOUSER list of usernames
SHOW just list file contents (N) TABLES list of table names
IGNORE ignore create errors (N) RECORDLENGTH length of IO record
GRANTS import grants (Y) INCTYPE incremental import type
INDEXES import indexes (Y) COMMIT commit array insert (N)
ROWS import data rows (Y) PARFILE parameter filename
LOG log file of screen output CONSTRAINTS import constraints (Y)
DESTROY overwrite tablespace data file (N)
INDEXFILE write table/index info to specified file
SKIP_UNUSABLE_INDEXES skip maintenance of unusable indexes (N)
FEEDBACK display progress every x rows(0)
TOID_NOVALIDATE skip validation of specified type ids
FILESIZE maximum size of each dump file
STATISTICS import precomputed statistics (always)
RESUMABLE suspend when a space related error is encountered(N)
RESUMABLE_NAME text string used to identify resumable statement
RESUMABLE_TIMEOUT wait time for RESUMABLE
COMPILE compile procedures, packages, and functions (Y)
STREAMS_CONFIGURATION import streams general metadata (Y)
STREAMS_INSTANTIATION import streams instantiation metadata (N)
DATA_ONLY import only data (N)
VOLSIZE number of bytes in file on each volume of a file on tape
The following keywords only apply to transportable tablespaces
TRANSPORT_TABLESPACE import transportable tablespace metadata (N)
TABLESPACES tablespaces to be transported into database
DATAFILES datafiles to be transported into database
TTS_OWNERS users that own data in the transportable tablespace set
Import terminated successfully without warnings.
[tkyte@localhost ~]$
read up on FROMUSER TOUSER parameters to see how to import a table into a schema other than the one it was exported from.
(capslock key is apparently stuck on your keyboard, it feels like you are shouting at me)
How to use EXP in 9i for a multiple JOIN condition.
Navya, July 03, 2013 - 2:14 pm UTC
Hi Tom, I would like to know, if there is a way to use EXP in 9i database for a condition like this:
select * from SYSADM.PS_DEDUCTION_BAL B
WHERE B.BALANCE_YEAR = '2013'
AND B.BALANCE_QTR = (SELECT MAX(A.BALANCE_QTR) FROM SYSADM.PS_DEDUCTION_BAL A WHERE
A.EMPLID = B.EMPLID AND
A.BALANCE_ID = B.BALANCE_ID AND
A.BALANCE_YEAR = B.BALANCE_YEAR AND
A.BENEFIT_RCD_NBR = B.BENEFIT_RCD_NBR AND
A.PLAN_TYPE = B.PLAN_TYPE AND
A.BENEFIT_PLAN = B.BENEFIT_PLAN AND
A.DEDCD = B.DEDCD AND
A.DED_CLASS = B.DED_CLASS AND
A.COMPANY = B.COMPANY) AND
B.BALANCE_PERIOD = (SELECT MAX(C.BALANCE_PERIOD) FROM SYSADM.PS_DEDUCTION_BAL C WHERE
C.EMPLID = B.EMPLID AND
C.BALANCE_ID = B.BALANCE_ID AND
C.BALANCE_YEAR = B.BALANCE_YEAR AND
C.BALANCE_QTR = B.BALANCE_QTR AND
C.BENEFIT_RCD_NBR = B.BENEFIT_RCD_NBR AND
C.PLAN_TYPE = B.PLAN_TYPE AND
C.BENEFIT_PLAN = B.BENEFIT_PLAN AND
C.DEDCD = B.DEDCD AND
C.DED_CLASS = B.DED_CLASS AND
C.COMPANY = B.COMPANY);
The reason behind not using "Create table as select * from" and then, exporting the newly created table is, we have a space constraint in the database and we have requirement like above for almost 50 tables, so, "Space Constraint" is not allowing us to do that!!
July 16, 2013 - 12:30 pm UTC
exp is very limited, it cannot export view data only view definitions.
your space constraint is preventing you from accomplishing your job apparently? maybe you can point out that you and the group waiting for this data have already cost more money than a tiny bit of disk....
it is very helpful to me
A reader, February 13, 2014 - 4:52 am UTC
it is very helpful to me i got what i want
query level export on date condition
vikas dhrmadhikari, April 28, 2015 - 11:30 am UTC
Hi Tom.
we can use condition in query clause that is ok
but what if i want to export date between tow dates so is ti possible with query? if yes please share the syntax
Thanks in advance
export with date
vikas dhrmadhikari, April 28, 2015 - 11:31 am UTC
hi Tom,
is it possible to to use query level export to export data between two dates if yes please share the syntax.
Thanks in advance
A reader, October 31, 2018 - 1:24 am UTC
thank you.
\ 문자 사용방법 때문에 고민 했는데 한번에 해결되었습니다.
Asim, May 30, 2019 - 7:27 am UTC
Hello Tom,
I am trying to export data using the query option with date parameter.
but what I observed the whenevr export data is mismatching with the date. ( date coming wrong)
exp bhs/bhs-1234@dial file=d:\LBT_BAGTRACKING.dmp tables=LBT_BAGTRACKING query= """where eventtime between to_date(to_char(sysdate-441,'yyyy-mm-dd')||' 15.00.00','yyyy-mm-dd HH24:MI
:SS') and to_date(to_char(sysdate-441,'yyyy-mm-dd')||' 15.01.00','yyyy-mm-dd HH24:MI:SS')"""
May 31, 2019 - 6:11 am UTC
1) can we see the DDL for lbt_bagtracking
2) also, I hate all the escaped double quotes etc with exp on the command line. Try using a parfile - a lot easier.
3) lets see what happens for a manually coded date, ie
to_date('2018-03-18 15:00:00')
Continued
Asim, May 30, 2019 - 7:34 am UTC
See below-
C:\Users\asimali0>exp bhs/bhs-1234@SCS file=d:\LBT_BAGTRACKING.dmp tables=LBT_BAGTRACKING query= """where eventtime between to_date(to_char(sysdate-441,'yyyy-mm-dd')||' 15.00.00','yyyy-mm-dd HH24:MI:
SS') and to_date(to_char(sysdate-441,'yyyy-mm-dd')||' 15.01.00','yyyy-mm-dd HH24:MI:SS')"""
Export: Release 11.2.0.1.0 - Production on Thu May 30 16:32:00 2019
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table LBT_BAGTRACKING 22021 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
Export terminated successfully with warnings.
While query result with the same parameter.
Select count(*) from lbt_bagtracking
where eventtime between to_date(to_char(sysdate-441,'yyyy-mm-dd')||' 15.00.00','yyyy-mm-dd HH24:MI:SS') and to_date(to_char(sysdate-441,'yyyy-mm-dd')||' 15.01.00','yyyy-mm-dd HH24:MI:SS')
Output 1339
Debugging ideas
lh, June 03, 2019 - 6:15 am UTC
Hi
Just few debugging ideas:
- what is the eventdatetime for those 'extra' rows ?
- have You looked at v$sql table and tried to identify the actual statement used to extract these rows with export?
lh
more questions...
lh, June 03, 2019 - 6:26 am UTC
Hi
Is eventtime date or timestamp column ?
Should the where clause actually be
where eventtime >= to_date(to_char(sysdate-441,'yyyy-mm-dd')||' 15.00.00','yyyy-mm-dd HH24:MI:SS')
and eventtime < to_date(to_char(sysdate-441,'yyyy-mm-dd')||' 15.01.00','yyyy-mm-dd HH24:MI:SS')
?
lh