Oracle 11.2.0.1 RAC using exp encountered ORA-01455 error Solution

1. Environment Description

OS: Linux RedHat5.5

Oracle version: Oracle RAC 11.2.0.1

2. Problem Description

Use exp to export data of multiple users in the database. The statement is exp buffer = 4096000 file = exp. dmp wner = user1, user2 ....

The ORA-1455: converting column overflows integer ype error is reported when the export ends at the end.

3. Solution steps

After this problem occurs, Baidu, google, and metalink can find the following methods, but they all solve the problem (the problem may be different. If you encounter this error, you can try several solutions)

1: add the index = n command in the exp command to solve this error.

2: The solution written in note [ID 1083330.1] In metalink is as follows:

Solution

On 11.2, by default deferred segment creation future is turned on. when a table created with locally managed tablespace then its property value is very big. if we insert any row in the table then it's segment gets created and property value updated to 536870912. supported workarounds for this problem are:

1. If a table is created in this manner it is required to contain data even if the data is then deleted afterwards.

Or:

2. Initially create the table with segment creation immediate option, or set the parameter:
<! -- [If! SupportLineBreakNewLine] -->
<! -- [Endif] -->

Alter system set deferred_segment_creation = false;

And create the table afterwards in a new session.
 

Deferred_segment_creation

Change the default value of TRUE to false.

I tried both methods and declared the failure. However, after other tests, I found that there are two other solutions, plus a specific case. It is unclear whether the solution is used.

1. after expert guidance from colleagues, because a materialized view exists in my database, if you delete this materialized view, it can be solved successfully. In addition, it is very strange that the materialized view cannot be deleted using tools such as pl/SQL. You must use the drop command to delete the materialized view. I solve this problem, but I don't know if it is a general problem.

 

2. Discard exp/imp and use the data pump EXPDP to export the data.

3. Big moves! You can Upgrade Oracle to version 11.2.0.3.