Resolving ORA-02374: conversion error loading table during impdp
Error:
ORA-02374: conversion
error loading table “SCHEMA”.”TABLENAME”
error loading table “SCHEMA”.”TABLENAME”
ORA-12899: value too large
for column MINE_TEXT (actual: 66, maximum: 64)
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
expdp and created a dump for the table: SCHEMA.TABLENAME
2. While
doing a impdp on the target database :
doing a impdp on the target database :
ORA-02374: conversion
error loading table “SCHEMA”.”TABLENAME”
error loading table “SCHEMA”.”TABLENAME”
ORA-12899: value too large
for column MINE_TEXT (actual: 66, maximum: 64)
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):
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)
NULL NUMBER(5)
MINE_IDX NOT
NULL NUMBER(5)
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:
modify the column of the table:
SQL> alter table SCHEMA.TABLENAME
modify MINE_TEXT CHAR(66);
modify MINE_TEXT CHAR(66);
Table altered.
Now perform the import
using the below command:
using the below command:
impdp
directory=DATA_PUMP_DIR dumpfile=MINE_USR.dmp logfile=MINE_USR.log
tables=SCHEMA.TABLENAME table_exists_action=truncate