OraFAQ Forum: Server Utilities » export table based on query and partition wise

Home » RDBMS Server » Server Utilities » export table based on query and partition wise (solaris 10 oracle 9.2.0.7)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

export table based on query and partition wise [message #320657]

Thu, 15 May 2008 15:21

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

Report message to a moderator

Re: export table based on query and partition wise [message #320660 is a reply to message #320657]

Thu, 15 May 2008 15:37

Michel Cadot
Messages: 68653
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

Report message to a moderator

Re: export table based on query and partition wise [message #320664 is a reply to message #320660]

Thu, 15 May 2008 16:00

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

Razz

Report message to a moderator

Re: export table based on query and partition wise [message #320667 is a reply to message #320657]

Thu, 15 May 2008 17:29

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

Smile

Report message to a moderator

Re: export table based on query and partition wise [message #320672 is a reply to message #320667]

Thu, 15 May 2008 18:06

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?

Report message to a moderator

Re: export table based on query and partition wise [message #320673 is a reply to message #320667]

Thu, 15 May 2008 18:11

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 .

Report message to a moderator

Re: export table based on query and partition wise [message #320675 is a reply to message #320673]

Thu, 15 May 2008 18:25

ebrian
Messages: 2794
Registered: April 2006

Senior Member

Check back:

exp -help

Report message to a moderator

Re: export table based on query and partition wise [message #320706 is a reply to message #320673]

Fri, 16 May 2008 01:03

Michel Cadot
Messages: 68653
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

Report message to a moderator

Re: export table based on query and partition wise [message #321052 is a reply to message #320706]

Sun, 18 May 2008 20:01

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

BlackSwan
Messages: 26766
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.

Report message to a moderator

Re: export table based on query and partition wise [message #321056 is a reply to message #321053]

Sun, 18 May 2008 20:53

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?

Report message to a moderator

Re: export table based on query and partition wise [message #321060 is a reply to message #320657]

Sun, 18 May 2008 21:11

BlackSwan
Messages: 26766
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)!

Report message to a moderator

Re: export table based on query and partition wise [message #321067 is a reply to message #321060]

Sun, 18 May 2008 22:11

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

Report message to a moderator

Re: export table based on query and partition wise [message #321069 is a reply to message #320657]

Sun, 18 May 2008 23:05

BlackSwan
Messages: 26766
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?

Report message to a moderator

Re: export table based on query and partition wise [message #321072 is a reply to message #321052]

Sun, 18 May 2008 23:33

Michel Cadot
Messages: 68653
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Don't put "\" in parameter file.

Regards
Michel

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri May 17 06:19:05 CDT 2024