Categories
Database Export/Import (EXPDP/IMPDP) Oracle

IMPDP Errors: ORA-02374, ORA-12899

If the Character set of source and destination database are not equal, you will see below errors in the impdp log file:

ORA-02374: conversion error loading table "SCHEMA1"."TABLE1"
ORA-12899: value too large for column COLUMN1 (actual: 101, maximum: 100)

to solve this error, you have to restore the structure of tables first

impdp schemas=schema1 dumpfile=dump.dmp logfile=log.log directory=dumps content=<b>METADATA_ONLY</b>
after that, you have to create a table and fill it with information about columns with have CHAR or VARCHAR datatype: 
CREATE TABLE SEMANTICS$
(S_OWNER VARCHAR2(40),
S_TABLE_NAME VARCHAR2(40),
S_COLUMN_NAME VARCHAR2(40),
S_DATA_TYPE VARCHAR2(40),
S_CHAR_LENGTH NUMBER);

INSERT INTO SEMANTICS$
 SELECT C.OWNER, C.TABLE_NAME, C.COLUMN_NAME, C.DATA_TYPE, C.CHAR_LENGTH
   FROM ALL_TAB_COLUMNS C, ALL_TABLES T
  WHERE C.OWNER = T.OWNER
    AND T.OWNER IN ('[b]SCHEMA1[/b]')
       -- ALL ORACLE PROVIDED USERS 
    AND C.TABLE_NAME = T.TABLE_NAME
    AND C.CHAR_USED = 'B' -- ONLY NEED TO LOOK FOR TABLES WHO ARE NOT YET CHAR SEMANTICS. 
    AND T.PARTITIONED != 'YES' -- EXCLUDE PARTITIONED TABLES
    AND C.TABLE_NAME NOT IN (SELECT TABLE_NAME FROM ALL_EXTERNAL_TABLES)
    AND C.DATA_TYPE IN ('VARCHAR2', 'CHAR');
  COMMIT; 

then you have to modify these fields with alter table command as below:

DECLARE
  CURSOR C1 IS
    SELECT * FROM SEMANTICS$;
  V_STATEMENT VARCHAR2(255);
  V_NC        NUMBER(10);
  V_NT        NUMBER(10);
BEGIN
  EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM SEMANTICS$'
    INTO V_NC;
  EXECUTE IMMEDIATE 'SELECT COUNT(DISTINCT S_TABLE_NAME) FROM SEMANTICS$'
    INTO V_NT;
  FOR R1 IN C1 LOOP
    V_STATEMENT := 'ALTER TABLE ' || R1.S_OWNER || '.' || R1.S_TABLE_NAME;
    V_STATEMENT := V_STATEMENT || ' MODIFY (' || R1.S_COLUMN_NAME || ' ';
    V_STATEMENT := V_STATEMENT || R1.S_DATA_TYPE || '(' || R1.S_CHAR_LENGTH;
    V_STATEMENT := V_STATEMENT || ' CHAR));';
    EXECUTE IMMEDIATE V_STATEMENT;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('DONE');
END;

finally, we you have to import data to these tables:

impdp schemas=schema1 dumpfile=dump.dmp logfile=log.log directory=dumps content=<b>DATA_ONLY</b>

Leave a Reply

Your email address will not be published. Required fields are marked *