Home arrow News arrow Database arrow Excluding tables while using EXP in Oracle
Main Menu
Home
Help Docs
Newbies
Corporates
Gnu/Linux Distros
Programming
Databases
Links
Search
Regular
News
Interviews
Aaj Ka Tip
Aaj Ka Command
Community
Blogs
Wiki
Forums
Services
Free Software
Latest
Popular

Excluding tables while using EXP in Oracle Print E-mail
Problem statement: How do we go about to exclude certain tables while using 'exp' in older versions of Oracle.

Solution: Although there is no option of using the EXCLUDE option in older versions of Oracle as we have in newer versions of Oracle (9i and above), the below mentioned trick might work

Run this query to get the list of tables to be imported. Specify the tables which are required to be EXCLUDED in the WHERE clause.This will require you to login in the schema from which the tables are to be exported.
Else use the 'all_tables' view with the OWNER option.

Select table_name
From User_tables
Where table_Name not in ('TABLE1','TABLE2', ...)

copy the output(list of tables) in a file called 'exclude.par'. copy the list of tables in the TABLE clause and braces () like TABLE=(TABLE3 TABLE4 TABLE5) in the par file.

Now the exclude.par file will only contain the list of tables EXCLUDING the required tables

use the exclude.par file in the 'exp' utility. For ex.

exp username/pass file=export.dmp paramfile=exclude.par

This we way we can avoid manually specifying the list of tables in the export command

Limitation: The above metnhod will only the TABLES, and skip other obects like functions,procedure,views etc. They have to be specified in the param file explicitly.
< Previous   Next >



gnulinuxclub
Join Us
About Us
Contact Us
Support Us
Acknowlegdement
Login(only for dev.)

Royalty Free Images

O'Reilly User Group discount!




Powered By GIMP GIMP
Contact Webmaster Copyleft 2011 gnulinuxclub.org