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
|
|
|
|
|
|
|
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 .
|
|
|
|
|
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 #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?
|
|
|
|
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
|
|
|
|
|