Home » RDBMS Server » Server Utilities » export table based on query and partition wise (solaris 10 oracle 9.2.0.7)
export table based on query and partition wise [message #320657] Thu, 15 May 2008 15:21 Go to next message
skumar.dba
Messages: 82
Registered: October 2007
Member
Experts,

i need small help. i want export table data based on query and partition wise.

for example i have a table called T which has 60 partitions(p1,p2,p3...). now my requirement is i need to export this table data based on query and partition wise

can i do like this:

exp username/password file =TP1.dmp log =TP1.log table=T.p1 query =\"where condition=\'f\'\" full=y

can any one help me how to mention partition name here.

Regards,
kumar
Re: export table based on query and partition wise [message #320660 is a reply to message #320657] Thu, 15 May 2008 15:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Try "exp help=y" there is an example of how to express partition.

Regards
Michel
Re: export table based on query and partition wise [message #320664 is a reply to message #320660] Thu, 15 May 2008 16:00 Go to previous messageGo to next message
skumar.dba
Messages: 82
Registered: October 2007
Member
Michel,

Thanks for help. i got it(TABLES=(T1:P1,T1:P2), if T1 is partitioned table).

really you helped me a lot, by giving hint on help. before i went through help but i did not concentrate on it.
but now i got it..


Thank you very much Razz
Re: export table based on query and partition wise [message #320667 is a reply to message #320657] Thu, 15 May 2008 17:29 Go to previous messageGo to next message
skumar.dba
Messages: 82
Registered: October 2007
Member
Michel,

How are you.need small help on this export syntax.

exp system/xxxxx file=opt/mis/oracle/txn1.dmp log=/opt/mis/oracle/txn1.log owner=txnmgr tables =txn:txn_0607 query =\"where PRIMARY_CARRIER_NAME=\'KDDI\' and rownum\<25\"

for testing purpose i am taking 25 records export. i am getting failed. could you please tell me where i am wrong.

error is
EXP-00028: failed to open opt/mis/oracle/txn1.dmp for write
Export file: expdat.dmp > txn2.dmp ( i am giving another name)

i am very sorry experts: because i should not post this query in backup and recovery. it should be in server utilities.

next time i never do this mistake... Smile
Re: export table based on query and partition wise [message #320672 is a reply to message #320667] Thu, 15 May 2008 18:06 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Verify opt/mis/oracle/txn1.dmp is a valid path. Did you mean to include a '/' at the beginning?
Re: export table based on query and partition wise [message #320673 is a reply to message #320667] Thu, 15 May 2008 18:11 Go to previous messageGo to next message
skumar.dba
Messages: 82
Registered: October 2007
Member
Michel,

previous error i got the solution. i am giving wrong path for my dump file. now i got different error.
exp system/xxxxx file =/opt/mis/oracle/txn1.dmp log=/opt/mis/oracle/txn1.log tables =txn:txn_0406 query =\"where PRIMARY_CARRIER_NAME=\'KDDI\' and rownum\<25\"

EXP-00011: SYSTEM.TXN does not exist
EXP-00051: "TXN_0406" - given partition or subpartition name is not part of "TXN" table



for this i found that this table owner is different. now i have to take exprot. how do i do this.

could you please provide some hint to fix .
Re: export table based on query and partition wise [message #320675 is a reply to message #320673] Thu, 15 May 2008 18:25 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Check back:

exp -help
Re: export table based on query and partition wise [message #320706 is a reply to message #320673] Fri, 16 May 2008 01:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at OWNER parameter.

Also: http://download.oracle.com/docs/cd/B10501_01/server.920/a96652/toc.htm, scroll down to "export parameters".

Regards
Michel
Re: export table based on query and partition wise [message #321052 is a reply to message #320706] Sun, 18 May 2008 20:01 Go to previous messageGo to next message
skumar.dba
Messages: 82
Registered: October 2007
Member
experts,

My intention is to take export table based on condition from one user. and i have to import that export dump file in other database in the different/same user
i am using parameter file to take export. i am getting following error. can any one resolve this issue.

could you please find details below.

$ exp system/xxxxx PARFILE=EXTN0406.par

my parameter file is

owner=scott
file=/opt/mis/oracle/txnt.dmp 
log=/opt/mis/oracle/txnt.log 
tables=TXN:TXN_0406
query=\"where PRIMARY_CARRIER_NAME=\'KDDI\' and rownum\<10000\"
rows=yes
compress=n 
constraints=y 
grants=n
statistics=none

i am getting following error
BTQISPRD-<ORACLE>-4% exp system/xxxxxx PARFILE=etxn_o406.par
LRM-00101: unknown parameter name 'PRIMARY_CARRIER_NAME'
LRM-00113: error when processing file 'etxn_o406.par'

EXP-00019: failed to process parameters, type 'EXP HELP=Y' for help
EXP-00000: Export terminated unsuccessfully
BTQISPRD-<ORACLE>-5% mv etxn_o406.par etxn0406.par


it is saying that failure to process parameter file. but if i am using same parameters in command line i am able to take export.
exp system/xxxxx file=/opt/mis/oracle/txnt.dmp log=/opt/mis/oracle/txnt.log tables=TXN:TXN_0406 query=\"where PRIMARY_CARRIER_NAME=\'KDDI\' and rownum\<10000\" statistics=none compress=n constraints=y grants=n


where i am doing wrong. can any one help me.

[Updated on: Sun, 18 May 2008 20:02]

Report message to a moderator

Re: export table based on query and partition wise [message #321053 is a reply to message #320657] Sun, 18 May 2008 20:14 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>where i am doing wrong.
The contents of the parameter file does NOT match what is on the command line.
Re: export table based on query and partition wise [message #321056 is a reply to message #321053] Sun, 18 May 2008 20:53 Go to previous messageGo to next message
skumar.dba
Messages: 82
Registered: October 2007
Member
when i am executing using parameter file. i am getting problem with query clause. i check the both parameter file and command
query is same.

the change i made in parameter file is owner=scott.

is this cause this problem?
or problem with where condition.?

is there any thing wrong in where condition?
Re: export table based on query and partition wise [message #321060 is a reply to message #320657] Sun, 18 May 2008 21:11 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Forgive me but I have a hard time knowing what to believe based upon what you have posted.

$ exp system/xxxxx PARFILE=EXTN0406.par
BTQISPRD-<ORACLE>-4% exp system/xxxxxx PARFILE=etxn_o406.par
LRM-00113: error when processing file 'etxn_o406.par'
BTQISPRD-<ORACLE>-5% mv etxn_o406.par etxn0406.par


How is it that there are at least THREE different filenames for what should be a single parameter file?

EXTN0406.par<>etxn_o406.par<>etxn0406.par

You supposedly posted contents for some parameter file,
but who knows which one & what it has to do with any error you posted?

You have been asked repeatedly to use CUT & PASTE to show the whole complete & uninterpreted session.

You're On Your Own (YOYO)!
Re: export table based on query and partition wise [message #321067 is a reply to message #321060] Sun, 18 May 2008 22:11 Go to previous messageGo to next message
skumar.dba
Messages: 82
Registered: October 2007
Member
Hai,

please find exact details. i want to use parameter file for different partition. i have to make parameter file and execute it. so i am looking to take export using parameter file
could you please help me.

please find modification i have made in parameter file and export command.


my parameter file is etxn0406.par

[B]owner=scott[/B]
file=/opt/mis/oracle/txnt.dmp 
log=/opt/mis/oracle/txnt.log 
tables=TXN:TXN_0406
query=\"where PRIMARY_CARRIER_NAME=\'KDDI\' and rownum\<10000\"
rows=yes
compress=n 
constraints=y 
grants=n
statistics=none

----
i am using parameter file like this:
$exp system/xxxx PARFILE=etxn0406.par

-- ERROR IS 
LRM-00101: unknown parameter name 'PRIMARY_CARRIER_NAME'
LRM-00113: error when processing file 'etxn0406.par'

--- I AM ABLE TO TAKE EXPORT WITH SAME QUERY WHICH I HAVE USED IN 
PARAMETER FILE.
exp system/xxxxx file=/opt/mis/oracle/txnt.dmp log=/opt/mis/oracle/txnt.log [B]tables=scott.TXN:TXN_0406[/B] query=\"where PRIMARY_CARRIER_NAME=\'KDDI\' and rownum\<10000\" statistics=none compress=n constraints=y grants=n


Re: export table based on query and partition wise [message #321069 is a reply to message #320657] Sun, 18 May 2008 23:05 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>it is saying that failure to process parameter file. but if i am using same parameters in command line i am able to take export.
>exp system/xxxxx file=/opt/mis/oracle/txnt.dmp log=/opt/mis/oracle/txnt.log tables=TXN:TXN_0406 query=\"where PRIMARY_CARRIER_NAME=\'KDDI\' and rownum\<10000\" statistics=none compress=n constraints=y grants=n

>--- I AM ABLE TO TAKE EXPORT WITH SAME QUERY WHICH I HAVE USED IN PARAMETER FILE.
>exp system/xxxxx file=/opt/mis/oracle/txnt.dmp log=/opt/mis/oracle/txnt.log tables=scott.TXN:TXN_0406 query=\"where PRIMARY_CARRIER_NAME=\'KDDI\' and rownum\<10000\" statistics=none compress=n constraints=y grants=n

Which one of the 2 statements above is true & why do the facts continue to morph?

Since when do actual command lines contain formatting characters?

Re: export table based on query and partition wise [message #321072 is a reply to message #321052] Sun, 18 May 2008 23:33 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't put "\" in parameter file.


Regards
Michel
Previous Topic: No required contents im view v$logmnr_contents
Next Topic: export / import timing difference
Goto Forum:
  


Current Time: Fri Dec 09 21:38:12 CST 2016

Total time taken to generate the page: 0.05583 seconds