Resolving ORA-02374, 12899, 02372 issues during data-migration with different characterset

Resolving ORA-02374, ORA-12899, ORA-02372 issues during data-migration with different database character set

-- Error in import log

ORA-02374: conversion error loading table "crm"."transaction"
ORA-12899: value too large for column DETAILS_NOTE (actual: 4024, maximum: 4000)
ORA-02372: data for row: DETAILS_NOTE: 0X'434552564943414C205350494E450D0A0D0A4D756C7469706C' 

Issue Description:

I have transaction data in table "crm.transaction" where my database Characterset is "WE8MSWIN1252". In this table "DETAILS_NOTE" column datatype is varchar2 where some of the column values fully occupied with 4000 characters. When I am trying to import using IMPDP with another characterset database "AL32UTF8" then above error is coming.


Root cause:

RCA: When data are coming from source character-set to target new character set, special charters taking 3 char space, so it is showing as "ORA-12899: value too large for column RESULT (actual: 4024, maximum: 4000)".

Solution :

Target Character-set : AL32UTF8

Method-1:

• Migrate your schema or database.
• Create dblink with souuce to target database.
• Disable constraints.
• alter the table as example shown:
          SQL> alter table crm.transaction modify DETAILS_NOTE  VARCHAR2(4000 CHAR);
• Now insert the whole data .

Method-2:

• Migrate your schema or database.
• Disable constraints.
• alter the table as example shown:
          SQL> alter table crm.transaction modify DETAILS_NOTE  VARCHAR2(4000 CHAR);
• import the table from logical dump with below format.

impdp full=N directory=data_pump dumpfile=PROD_full_%U.dmp logfile=PROD_trx_imp.log TABLE_EXISTS_ACTION=TRUNCATE schemas=CRM TABLES=CRM.transaction REMAP_TABLE=HR.transaction:transaction

See the DDL of the Table after alter:

create table crm.transaction
(
  trnxid      NUMBER not null,
  rsltrpthdrid    NUMBER not null,
  paramdetailid    NUMBER not null,
  details_note     VARCHAR2(4000 CHAR),
  unitid      NUMBER,
) tablespace CRM_TBLS;

Thanx