ORA-12899: value too large for column

How to solve Below error ( Useful for Golden gate Replication when character set did not match)

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
ORA-02374: conversion error loading table "PMUS"."HTTSKY"
ORA-12899: value too large for column FKTTFILENM(actual: 52, maximum: 50)



Source:
---------
SQL> SELECT VALUE FROM V$NLS_PARAMETERS WHERE PARAMETER='NLS_CHARACTERSET';

VALUE
-----------------
EE8MSWIN1250


Destination:
----------------
SQL> SELECT VALUE FROM V$NLS_PARAMETERS WHERE PARAMETER='NLS_CHARACTERSET';


VALUE
----------
AL32UTF8




1) Import Metadata of the table

$ impdp system directory=BACKUP dumpfile=exp.dmp remap_tablespace=PMUS:PMUS content=metadata_only 




Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 11:04:05


2) Check any External, IOT tables ans invalid objects

SQL> 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 = 'PMUS'
  and c.table_name = t.table_name
  and c.char_used = 'B'
  and t.partitioned='YES'
  and c.table_name not in (select table_name from all_external_tables)
  and c.data_type in ('VARCHAR2', 'CHAR');

SQL> select INDEX_NAME , INDEX_TYPE, TABLE_OWNER, TABLE_NAME, STATUS, FUNCIDX_STATUS
 from ALL_INDEXES
 where INDEX_TYPE not in ('NORMAL', 'BITMAP','IOT - TOP')
 and TABLE_OWNER = 'PMUS'
 and TABLE_NAME in
 (select unique (table_name) from dba_tab_columns where char_used ='B');

SQL> SELECT OBJECT_NAME, OBJECT_TYPE, STATUS FROM DBA_OBJECTS WHERE STATUS ='INVALID' AND OWNER='PMUS'; 



3) Create table for Schema table columns



SQL> CREATE TABLE PMUS_COLUMNS(S_OWNER VARCHAR2(40), S_TABLE_NAME VARCHAR2(40),S_COLUMN_NAME VARCHAR2(40), S_DATA_TYPE VARCHAR2(40), S_CHAR_LENGTH NUMBER); 


4)  Insert  all column details of schema  (only varchar and char)

SQL> insert into PMUS_COLUMNS
 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 = 'PMUS'
 and C.table_name = T.table_name
 and C.char_used = 'B'
 and T.partitioned != 'YES'
 and C.table_name not in (select table_name from all_external_tables)

  and C.data_type in ('VARCHAR2', 'CHAR');





5)Convert Column Type Byte to Char

SQL> set serveroutput on
SQL> set termout on
SQL> declare
 cursor c1 is select * from PMUS_COLUMNS;
 v_statement varchar2(255);
 v_nc number(10);
 v_nt number(10);
 begin
 execute immediate 'select count(*) from PMUS_COLUMNS' into v_nc;
 execute immediate 'select count(distinct s_table_name) from PMUS_COLUMNS' into v_nt;
 dbms_output.put_line ('ALTERing ' || v_nc || ' columns in ' || v_nt || ' tables');
  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))';
  dbms_output.put_line(v_statement);
  execute immediate v_statement;
  end loop;
  dbms_output.put_line('Done');
  end;
  /
ALTERing 20 columns in 2 tables
ALTER TABLE "PMUS"."HTTSKY" modify ("FKTTFUTS4" CHAR(50 CHAR));
ALTER TABLE "PMUS"."HTTSKY" modify ("FKTTFUTS3" CHAR(50 CHAR));
ALTER TABLE "PMUS"."HTTSKY" modify ("FKTTFUTS2" CHAR(10 CHAR));
ALTER TABLE "PMUS"."HTTSKY" modify ("FKTTFUTS1" CHAR(10 CHAR));
ALTER TABLE "PMUS"."HTTSKY" modify ("FKTTFILENM" CHAR(254 CHAR));
ALTER TABLE "PMUS"."HTTSKY" modify ("HYQUNAM" CHAR(30 CHAR));
ALTER TABLE "PMUS"."HTTSKY" modify ("FKTTITNM" CHAR(50 CHAR));
ALTER TABLE "PMUS"."HTTSKY" modify ("HYUSER" CHAR(10 CHAR));
ALTER TABLE "PMUS"."HTTSKY" modify ("HYLNGP" CHAR(2 CHAR));
ALTER TABLE "PMUS"."HTTSKY" modify ("HYTXKY" VARCHAR2(254 CHAR));
ALTER TABLE "PMUS"."HTTSKY" modify ("HYOBNM" CHAR(10 CHAR));
ALTER TABLE "PMUS"."HTTSKYT" modify ("HYISFL" CHAR(1 CHAR));
ALTER TABLE "PMUS"."HTTSKYT" modify ("HYISTM" CHAR(1 CHAR));
ALTER TABLE "PMUS"."HTTSKYT" modify ("HYPNTC" CHAR(1 CHAR));
ALTER TABLE "PMUS"."HTTSKYT" modify ("HYMUSE" CHAR(10 CHAR));
ALTER TABLE "PMUS"."HTTSKYT" modify ("HYCRTU" CHAR(10 CHAR));
ALTER TABLE "PMUS"."HTTSKYT" modify ("HYTXPO" VARCHAR2(254 CHAR));
ALTER TABLE "PMUS"."HTTSKYT" modify ("HYLNGP" CHAR(2 CHAR));
ALTER TABLE "PMUS"."HTTSKYT" modify ("HYTXKY" VARCHAR2(254 CHAR));
ALTER TABLE "PMUS"."HTTSKYT" modify ("HYOBNM" CHAR(10 CHAR));
Done
PL/SQL procedure successfully completed.

SQL>


SQL> DROP TABLE PMUS_COLUMNS;

Table dropped.


6) Import the table data

$ impdp systemr directory=DATA_PUMP dumpfile=EXP.DMP remap_tablespace=PMUS:PMUS content=data_only

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "PMUS"."HTTSKYT" 6.610 GB 41961289 rows
. . imported "PMUS"."HTTSKYTT" 3.231 GB 41154151 rows
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 11:50:42