Excluding tables while using EXP in Oracle


Problem statement: How do we go about to exclude certain tables while using ‘exp’ in older versions of Oracle.

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

Leave a Reply

Your email address will not be published. Required fields are marked *