Home » RDBMS Server » Server Utilities » EXCLUDING DATA OF SOME TABLES in EXPDP (ORACLE 11gr2 linux)
EXCLUDING DATA OF SOME TABLES in EXPDP [message #597498] Sat, 05 October 2013 03:10 Go to next message
sakamboh
Messages: 19
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.
Re: EXCLUDING DATA OF SOME TABLES in EXPDP [message #597499 is a reply to message #597498] Sat, 05 October 2013 03:12 Go to previous messageGo to next message
Littlefoot
Messages: 19536
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Use expdp's QUERY clause.
Re: EXCLUDING DATA OF SOME TABLES in EXPDP [message #597502 is a reply to message #597498] Sat, 05 October 2013 03:43 Go to previous messageGo to next message
sakamboh
Messages: 19
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 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2207
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
Re: EXCLUDING DATA OF SOME TABLES in EXPDP [message #597505 is a reply to message #597498] Sat, 05 October 2013 04:33 Go to previous messageGo to next message
sakamboh
Messages: 19
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.
icon5.gif  Re: EXCLUDING DATA OF SOME TABLES in EXPDP [message #597512 is a reply to message #597505] Sat, 05 October 2013 05:43 Go to previous messageGo to next message
Michel Cadot
Messages: 58954
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Thanks, i got the issues and resolved it.


How? Post the command that works.

Re: EXCLUDING DATA OF SOME TABLES in EXPDP [message #597515 is a reply to message #597512] Sat, 05 October 2013 05:52 Go to previous messageGo to next message
sakamboh
Messages: 19
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"'


icon14.gif  Re: EXCLUDING DATA OF SOME TABLES in EXPDP [message #597519 is a reply to message #597515] Sat, 05 October 2013 06:24 Go to previous messageGo to next message
Michel Cadot
Messages: 58954
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Thanks to let us know.

Re: EXCLUDING DATA OF SOME TABLES in EXPDP [message #597520 is a reply to message #597519] Sat, 05 October 2013 06:28 Go to previous messageGo to next message
sakamboh
Messages: 19
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
icon2.gif  Re: EXCLUDING DATA OF SOME TABLES in EXPDP [message #597523 is a reply to message #597520] Sat, 05 October 2013 07:55 Go to previous message
Michel Cadot
Messages: 58954
Registered: March 2007
Location: Nanterre, France, http://...
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.

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


Current Time: Mon Sep 01 21:34:42 CDT 2014

Total time taken to generate the page: 0.09864 seconds