SOLVED

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

Asked by OracleDba••11 views•oracle
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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)".
#oracle#error

Solutions(1)

Accepted Solution
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
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
OracleDba•

Post Your Solution