Saturday, December 8, 2012


ORA-01455: converting column overflows integer datatype


ORA-01455: converting column overflows integer datatype

Error:
Export of 11g Rel2 using 10g Client will have the above error message at exporting triggers.


Error:
Export of 11g Rel2 using 10g Client will have the above error message at exporting triggers.

ORA-01455: converting column overflows integer datatype

EXP-00000: Export terminated unsuccessfully

Cause:
This is an Oracle Bug. The root cause is that the converted form of the specified expression was too large for the specified datatype.

Solution:

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.



ORA-1455 is raised when exporting from an 11.2 database using a 9i,10g or 11gR1 exp utility


Solution

Use the Datapump export utility (EXPDP) instead of traditional EXP.

OR

Run the below sql to see any empty table present with no extent allocated.

SQL> SELECT owner, table_name
     FROM dba_tables
     WHERE segment_created = 'NO'
     AND owner NOT IN ('ORDSYS', 'MDSYS', 'CTXSYS', 'ORDPLUGINS',
                       'LBACSYS', 'XDB', 'SI_INFORMTN_SCHEMA', 'DIP',
                       'DBSNMP', 'EXFSYS', 'WMSYS', 'ORACLE_OCM',
                       'ANONYMOUS', 'XS$NULL', 'APPQOSSYS')
     ORDER by 1, 2;


Allocate at least 1 extent for the offending tables reported in the above query using:
SQL> ALTER TABLE ... ALLOCATE EXTENT;

OR
Recreate the source 11.2 database with the initialization parameter DEFERRED_SEGMENT_CREATION=FALSE.

No comments:

Post a Comment