Blog

Learnomate Technologies > Blog > Oracle DBA > Resolving ORA-02374, 12899, 02372 issues during data-migration with different characterset

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

  • November 21, 2021
  • Posted by: Ankush Thavali
  • Category: Oracle DBA
No Comments

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

ORA-02372 ORA-02374 ORA-12899