Loading...

Tuesday, July 10, 2012

IMP-00019: row rejected due to ORACLE error 12899

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

Did you get ORA-12899 and its associated IMP-00019 while attempting to import an old-style EXP dump? 

IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "TEST"."TMAGANFS"."DESCR" (actual: 51, maximum: 50)

The reason is quite simple: the exported dump comes from a database whose character set is different from the one where you are attempting to import the data into *AND* one or more characters in the offending string in the source database needed fewer bytes than they need now in the target database.

For example, an accented character like 'à' in a WE8ISO8859P1 database requires just 1 byte, but in a AL32UTF8 database it needs two bytes:

select lengthb('à') l , rawtohex('à') u from dual;

L U  
- ----
2 C3A0

The error however is caused by the fact that the receiving column has been defined in bytes, for instance 50 bytes as in my case, whereas the total length, in bytes, is now 51).

The quick fix, that is far from being optimal, at least in my view, is to enlarge the receiving column and in order to do so, you might need to split the data import in three phases:
  1. in the first phase you just create the objects without importing the data (IMP parameter ROWS=N)
  2. in the second phase, you enlarge the affected columns
  3. in the third phase, you import the dump again, specifying the parameter IGNORE=Y, because the existing objects would make the import fail otherwise.
See message translations for ORA-12899 and search additional resources.

No comments:

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles