Resolving ORA-02374: conversion error loading table during impdp

Error:

ORA-02374: conversion
error loading table  “SCHEMA”.”TABLENAME”
ORA-12899: value too large
for column MINE_TEXT (actual: 66, maximum: 64)

ORA-02372: data for row:
MINE_TEXT : 0X’45737061C3B16F6C2020202020202020202020202020202020′

More About the Error:
1.        Used
expdp and created a dump for the table: SCHEMA.TABLENAME
2.        While
doing a impdp on the target database :
ORA-02374: conversion
error loading table “SCHEMA”.”TABLENAME”
ORA-12899: value too large
for column MINE_TEXT (actual: 66, maximum: 64)

ORA-02372: data for row:
MINE_TEXT : 0X’45737061C3B16F6C2020202020202020202020202020202020′

Solution:
When you describe the
table, you will see that the column MINE_TEXT is char(64):
SQL> desc  SCHEMA.TABLENAME
 Name                                      Null?    Type
 —————————————–
——– —————————-
 MINE_IDX                                NOT
NULL NUMBER(5)
 MINE_IDX                                 NOT
NULL NUMBER(5)
 MINE_COLUMN                                     NUMBER(5)
 MINE_VALUE                                      NUMBER(5)
 MINE_TEXT                                         CHAR(64)
Execute the below to
modify the column of the table:
SQL> alter table SCHEMA.TABLENAME
modify MINE_TEXT CHAR(66);
Table altered.
Now perform the import
using the below command:

impdp
directory=DATA_PUMP_DIR dumpfile=MINE_USR.dmp logfile=MINE_USR.log
tables=SCHEMA.TABLENAME table_exists_action=truncate

  • January 22, 2019 | 22 views
  • Comments