- 21 Nov, 2021
- 0 Comments
- 1 Min Read
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:
1 | 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:
1 | SQL> alter table crm.transaction modify DETAILS_NOTE VARCHAR2(4000 CHAR); |
import the table from logical dump with below format.
1 | 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:
1 2 3 4 5 6 7 8 | 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; |