ORA-02374: Conversion error loading table, ORA-02374: Conversion error loading table during impdp

ORA-02374: Conversion error loading table, ORA-02374: Conversion error loading table during impdp

During import data through impdp on target database facing the below error:-

ORA-02374: conversion error loading table "FINNONELOS"."LOS_DEDUPE_CUSTOMER_HST"

ORA-12899: value too large for column PROPERTY_ADDR1 (actual: 102, maximum: 100)

 

ORA-02372: data for row: PROPERTY_ADDR1 : 0X'464C4154204E4F2E203530312C203520544820464C4F4F5220'

 


Import completed with error [ i.e partially imported data in "FINNONELOS"."LOS_DEDUPE_CUSTOMER_HST table]

Follow the below steps for the solution:-

o   Describe the table and modify the column size accordingly.

SQL> desc FINNONELOS.LOS_DEDUPE_CUSTOMER_HST

 Name                                      Null?    Type

 ----------------------------------------- -------- -----------------

 ..

 ..

 PROPERTY_ADDR1                                     VARCHAR2(100)

 ..

 

SQL> alter table FINNONELOS.LOS_DEDUPE_CUSTOMER_HST modify PROPERTY_ADDR1 VARCHAR2(105);

 

Modify the column size from VARCHAR2(100)to VARCHAR2(105).

o   Now going to perform the import using the below script for particular table.

 

impdp system/password@FINDEV directory=IMPDP dumpfile=findev_expdp_%U.dmp tables=FINNONELOS.LOS_DEDUPE_CUSTOMER_HST logfile=DEDUPE_CUSTOMER_HST.log table_exists_action=truncate

  


Now Import of FINNONELOS.LOS_DEDUPE_CUSTOMER_HST table has been successfully completed without any error.

 


Note: - Mainly the error occurred when the source (expdp) and target (impdp) database character sets are different, for checking the same use the below views

select value,parameter from NLS_DATABASE_PARAMETERS where PARAMETER like '%CHARACTERSET';

 

 Some more articles you might also be interested in :-

   Distributed transaction in oracle

   ORA-02050: transaction rolled back, some remote DBs may be in-doubt

   ORA-01591: lock held by in-doubt distributed transaction

   ORA-31693: Table data object failed to load/unload in expdp

 -

Comments

Post a Comment

Popular posts from this blog

Remove ACL and privileges, Drop ACL in oracle

Image
  Remove ACL and privileges, Drop ACL in oracle See the Network and ACL assignments as below -                                    o    Unassign ACL           begin              dbms_network_acl_admin.unassign_acl(              acl   => 'utl_mail_test.xml',              host => '172.16.20.18',                lower_port   => 25,              upper_port   => NULL              );              commit;          end; ...
Read more

ORA-02050: transaction rolled back, some remote DBs may be in-doubt

ORA-02050: transaction rolled back, some remote DBs may be in-doubt Indicates that a communication error occurred during the two-phase commit. The two phase-commit mechanism is used to ensure data integrity in a distributed transaction. Example of handling the In-Doubt Transactions - in this example we are taking two servers ACCT and FIN.   Got error ORA-02050: transaction rolled back, some remote DBs may be in-doubt at FIN Server Scenario- LOCAL_TRAN_ID and GLOBAL_TRAN_ID are same.   Execute below query in SYS user at FIN server. SQL> SELECT LOCAL_TRAN_ID , STATE FROM dba_2pc_pending ; LOCAL_TRAN_ID  GLOBAL_TRAN_ID         STATE -------------   --------------- ---------------- 3.12 .2 54         --------. 3.12 . 254    collecting   Here local id and Global id is same, so it is the global coordinator. State column is collecting means this ...
Read more

Distributed transaction, Oracle Distributed Transactions, distributed transaction in oracle

Distributed transaction, Oracle Distributed Transactions, distributed transaction in oracle A distributed transaction modifies data related to two or more databases, it contains DML statements than span many nodes. For a distributed transaction to be successful all or none of the database nodes involved in the transaction need to commit or rollback the whole transaction. Note the difference between a distributed and a remote transaction; a remote transaction contains one or more DML statements that are executed on the SAME remote node, consider the examples below:- Example of Distributed transaction: insert into table@remotesite; insert into mytable: --local table commit;   Example of Remote transaction: insert into table@remotesite; commit;   Database links are used to communicate between the databases performing distributed transactions. In a distributed environment, it is fundamental that the global name of a database is unique in the network so that eac...
Read more