Please visit www.oracle-class.com for Videos, Free posts, Books, Webinar and Free forum … ALL about Oracle!

How do I fix ORA-01455 error on 11g Release 2 oracle database ?

INTRODUCTION

Today, using my test Oracle 11g Release 2 on one virtual machine, to test the export of one schema.
I was exporting schema n 11.2 database With 10g Client. The export Gave an Ora-01455.

The export failed with the following detailed error :
….
. exporting referential integrity constraints
. exporting triggers
EXP-00056: ORACLE error 1455 encountered
ORA-01455: converting column overflows integer datatype
EXP-00000: Export terminated unsuccessfully
…..

I remember I have solved this issue one month ago when one Oracle user posted the issue on the OTN forum. I help him to fix the issue.
After fixing the problem, I thought it would be a good idea to post the solution here so everyone can solve it in the future;

THE ERROR

. exporting referential integrity constraints
. exporting triggers
EXP-00056: ORACLE error 1455 encountered
ORA-01455: converting column overflows integer datatype
EXP-00000: Export terminated unsuccessfully

This is an Oracle Bug. The root cause is that the converted form of the specified expression was too large for the specified datatype. (from ora-code.com web site).

SOLUTION

I have fixed the problem using one of the following solutions, Therefore, this cannot offer any guarantee of fix for such situations.

Solution 1 : Try with an Oracle 11g Release 2 EXP client against the Oracle 11g Release 2.
Solution 2 : Use the triggers=n option, that allows an export of a schema made by an Oracle 10.2 client against an Oracle 11g release 2 to complete successfully.

The article is also posted here in Oracle mix :
https://mix.oracle.com/group_messages/149104-how-do-i-fix-ora-01455-error-on-11g-release-oracle-database

Nice Reading and Thank you,
Wissem EL KHLIFI

4 Comments »

  1. sqlplus / as sysdba
    sql>noaudit session

    Then export again.

    Comment by Wendy — April 1, 2011 @ 7:55 pm | Reply

  2. Hi

    You said Triggers=n, but i need all the triggers with my Export.
    Is there any other solution.

    Regards
    Neeraj Bansal

    Comment by Neeraj Bansal — May 21, 2011 @ 12:50 pm | Reply

  3. Tried the above method the problem remains the same.

    EXP-00056: ORACLE error 1455 encountered
    ORA-01455: converting column overflows integer datatype
    EXP-00000: Export terminated unsuccessfully

    Comment by Narasimha — July 18, 2011 @ 10:37 am | Reply

  4. recompile any triggers,views etc, or
    where the exception occurs

    .exporting referential integrity constraints
    . exporting triggers
    EXP-00056: ORACLE error 1455 encountered

    recompile invalid triggers;
    retry exp

    Comment by Sonerb — August 16, 2011 @ 11:26 am | Reply


RSS feed for comments on this post. TrackBack URI

Leave a comment

Create a free website or blog at WordPress.com.