How can I fix oracle exp table exclude?

Asked By gustavojakee 10 points N/A Posted on - 01/25/2013

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?

• Status: Open
• Question Views: 1984
• Answer Count: 2
• Vote Up 0 Vote Down

• Answer Accepted: Yes
• Question Category: Oracle Database

Best Answer by Luker Malcom

Go To Solution

Best Answer

Best Answer

Answered By Luker Malcom 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.

About Luker Malcom

Questions
0

Answers
173

Best Answers
108

• Vote Up 0 Vote Down

• Posted on - 02/02/2013
• Question Category: Oracle Database

Answered By Williams Todd 0 points N/A #195991

How can I fix oracle exp table exclude?

Hello,

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/passw@db 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 
EXCLUDE=TABLE:"NOT IN ('TEMP','TEMP1')" 
Using the IN OPERATOR 
EXCLUDE=TABLE:"IN ('TEMP','TEMP1')" 

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.

About Williams Todd

Questions
4

Answers
143

Best Answers
18

• Vote Up 0 Vote Down

• Posted on - 02/14/2013
• Question Category: Oracle Database