How can I fix oracle exp table exclude?

Asked By 10 points N/A Posted on -

I am using exp table to backup my own tables. I have schema about 1000 tables where the list can be changed.

There is only  one table that is huge in size and I need to export and exclude it.

How can I fix oracle exp table exclude?

Best Answer by Luker Malcom
Best Answer
Best Answer
Answered By 5 points N/A #142290

How can I fix oracle exp table exclude?


In order to exclude certain tables in Oracle using EXP, here is what you can do:

  • Run the query to get a list of tables you what to import. 
  • Put the tables you want to be excluded in the WHERE clause like this:

Select table_name

From User_table

Where table_name not in ('Table 1', 'Table2,…)

  • Then copy the output list of tables and save it in a file EXCLUDE.PAR. this will only contain the excluded tables that You put inside the WHERE clause.
  • Use the EXCLUDE.PAR file in EXP utility.

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

This command will only exclude TABLES and not functions, view, etc.

If you want them to be excluded, you should specify them explicitly just like what you did in the tables.

Answered By 0 points N/A #195991

How can I fix oracle exp table exclude?



Here is an example that shows you how to exclude tables from a dump. You should use you user and password to access the database directory to the dump file and use export and exclude as shown below:

C:Users>expdp user/[email protected] directory=data_pump_dir dump 
file=export_test.dmp full=y logfile=export_test.log EXCLUDE=TABLE:"LIKE 'OP%'", TABLE:"LIKE '%XYZ%'" 

Other options in expdpwill exclude the following:  Using the NOT IN OPERATOR 
Using the IN OPERATOR 

To avoid more errors, it is necessary to use back slashes. The following errors could occur:
ORA-39001: invalid argument value.
ORA-39071: Value for EXCLUDE is badly formed.

Hope this is helpful, good luck.

Login/Register to Answer

Related Questions