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>