OraFAQ Forum: Server Utilities » EXCLUDING DATA OF SOME TABLES in EXPDP

Home » RDBMS Server » Server Utilities » EXCLUDING DATA OF SOME TABLES in EXPDP (ORACLE 11gr2 linux)

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

EXCLUDING DATA OF SOME TABLES in EXPDP [message #597498]

Sat, 05 October 2013 03:10

sakamboh
Messages: 24
Registered: March 2013
Location: oman

Junior Member

Hello,

Can anyone guide me, i want to exclude only data of some particular tables not complete table object when exporting using expdp. Will be thankful for any advise / solution.

Report message to a moderator

Re: EXCLUDING DATA OF SOME TABLES in EXPDP [message #597499 is a reply to message #597498]

Sat, 05 October 2013 03:12

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Use expdp's QUERY clause.

Report message to a moderator

Re: EXCLUDING DATA OF SOME TABLES in EXPDP [message #597502 is a reply to message #597498]

Sat, 05 October 2013 03:43

sakamboh
Messages: 24
Registered: March 2013
Location: oman

Junior Member

Thanks,

Please clarify the below queries as well... What needs to be changed in the below query to make it work.

expdp USER/PASS@sid schemas=scott dumpfile=ABC.dmp logfile=ABC.log query=table1,table2,table3:'"where rownum = 0"'

Its giving below error:
ORA-39001: invalid argument value
ORA-39035: Data filter SUBQUERY has already been specified.

Secondly please let me know if this can impact on resource usage and is there any performance issues that can occur if i mentioned around 10 tables in query. I am asking because i am going to execute this on my real environment.

Thanks.

[Updated on: Sat, 05 October 2013 03:49]

Report message to a moderator

Re: EXCLUDING DATA OF SOME TABLES in EXPDP [message #597503 is a reply to message #597502]

Sat, 05 October 2013 04:18

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Syntax -

QUERY = [schema.][table_name:] query_clause

Put the quotes properly. Search the forum for "query with expdp", you will get plenty of examples.

Regards,
Lalit

Report message to a moderator

Re: EXCLUDING DATA OF SOME TABLES in EXPDP [message #597505 is a reply to message #597498]

Sat, 05 October 2013 04:33

sakamboh
Messages: 24
Registered: March 2013
Location: oman

Junior Member

Thanks, i got the issues and resolved it. Please provide me some more information as requested earlier.

Quote:

Secondly please let me know if this can impact on resource usage and is there any performance issues that can occur if i mentioned around 10 tables in query. I am asking because i am going to execute this on my real environment.

Thanks in advance.

Report message to a moderator

Re: EXCLUDING DATA OF SOME TABLES in EXPDP [message #597512 is a reply to message #597505]

Sat, 05 October 2013 05:43

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

Thanks, i got the issues and resolved it.

How? Post the command that works.

Report message to a moderator

Re: EXCLUDING DATA OF SOME TABLES in EXPDP [message #597515 is a reply to message #597512]

Sat, 05 October 2013 05:52

sakamboh
Messages: 24
Registered: March 2013
Location: oman

Junior Member

Find below the command that worked for me.

Quote:

expdp user/password@sid schemas=sms dumpfile=abc.dmp logfile=abc.log query=TABLE1:'"where rownum = 0"',TABLE2:'"where rownum = 0"',TABLE3:'"where rownum = 0"'

Report message to a moderator

Re: EXCLUDING DATA OF SOME TABLES in EXPDP [message #597519 is a reply to message #597515]

Sat, 05 October 2013 06:24

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Thanks to let us know.

Report message to a moderator

Re: EXCLUDING DATA OF SOME TABLES in EXPDP [message #597520 is a reply to message #597519]

Sat, 05 October 2013 06:28

sakamboh
Messages: 24
Registered: March 2013
Location: oman

Junior Member

Micheal,

Can you help me with the my below query.

Currently i am able to generate a full export without any issues, i need to know is that by adding these queries is there any overhead or extra resource utilization i can face or this will proceed as normal full export.

Regards,
Sakamboh

Report message to a moderator

Re: EXCLUDING DATA OF SOME TABLES in EXPDP [message #597523 is a reply to message #597520]

Sat, 05 October 2013 07:55

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

I don't know, you have to try it to know as it surely depends on many things in the environment.

Report message to a moderator

Previous Topic:	SQL LOADER
Next Topic:	EXPDP with query error

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Feb 11 18:22:31 CST 2025