Oracle 11g -- ORA-02374 / ORA-12899 / ORA-02372 While Importing Data Using IMPDP

Problem

You are performing import into a newly created Oracle database. For some objects you are receiving errors like:

ORA-02374: conversion error loading table "SCHEMA_NAME"."TABLE_NAME"
ORA-12899: value too large for column COLUMN_NAME (actual: 256, maximum: 255)
ORA-02372: data for row: COLUMN_NAME : VALUE

Solution

Check the NLS_CHARACTERSET parameter on the old and new databases:

SOURCE DATABASE: WE8MSWIN1252
TARGET DATABASE: AL32UTF8

UTF8/AL32UTF8 is a varying width characterset, which means that the code for 1 character can be 1 , 2 , 3 or 4 bytes long. This is a big difference with character sets like WE8ISO8559P1 or WE8MSWIN1252 where 1 character is always 1 byte.

This also means that the columns need to be big enough to store the additional bytes.

The fastest and simplest way to solve the problem is to re-create the new database with characterset parameters matching the parameters on the old database.

If you do need to use multibyte charactersets then consult Metalink articles for more information:

• ORA-01401 / ORA-12899 / ORA-01461 While Importing Or Loading Data In An AL32UTF8 / UTF8 (Unicode) Or Other Multibyte NLS_CHARACTERSET Database. (Doc ID 1297961.1)
• Changing the NLS_CHARACTERSET to AL32UTF8 / UTF8 (Unicode) in 8i, 9i , 10g and 11g (Doc ID 260192.1)