ORA-12899: value too large for column "SCHEME". "TABLE". "ROW" (actual: 51, maximum: 50)

Source: http://albertolarripa.com/2012/06/10/ora-12899-changing-columns-to-char/

Error: Error occurred when Oracle database import.

Column 12 LA APLICACION EN ESPA?A DEL CONVENIO DE LA HAYA DE...

IMP-00019: row Rejected due to Oracle error 12899

IMP-00003: ORACLE Error 12899 Encountered

ORA-12899: value too large for column "SCHEME". "TABLE". "ROW" (actual: 51, maximum: 50)

Solution:

It means source db and target db char set not same. Target table data type may be used as byte (which every special character consider two byte). It should be char. Flow the below process for solution.

Table definitions

Import only the definitions of the tables, no the rows

SQL> impdp system/password schemas=SCOTT directory=MY_BCK dumpfile=SCOTT.dmp logfile=impdpSCOTT.log content=metadata_only

SQL> imp system/password fromuser=SCOTT touser=SCOTT file=/u01/app/oracle/bck/SCOTT.dmp log=/u01/app/oracle/bck/expdpSCOTT.log ignore=Y ROWS=N

CHAR conversion

 

To facilitate this process, Oracle created this script. You only need to edit the name of your scheme:

Conn / as sysdba

set feedback off

set verify off

set serveroutput on

set termout on

exec dbms_output.put_line('Starting build select of columns to be altered');

drop table semantics$

/

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

('SCOTT')

-- 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')

-- You can exclude or include tables or shema's as you wish, by adjusting

-- "and T.owner not in" as per your requirements

/

commit

/

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;

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))';

execute immediate v_statement;

end loop;

dbms_output.put_line('Done');

end;

/

Recompile the scheme to validate dependent objects:

SQL> EXECUTE UTL_RECOMP.RECOMP_PARALLEL(4);

Import Data

To finish importing the data scheme

SQL> impdp system/password schemas=SCOTT directory=MY_BCK dumpfile=SCOTT.dmp logfile=impdpSCOTT.log

SQL> imp system/password fromuser=SCOTT touser=SCOTT file=/u01/app/oracle/bck/SCOTT.dmp 

Another way:

Source: http://mtalmasri.blogspot.com/2013/10/oracle-ora-12899-value-too-large-for.html

Oracle: ORA-12899: value too large for column during imp from WE8MSWIN1252 to AL32UTF8

During migration/import from 9i(WE8ISO8859P1) to 11g(AL32UTF8), the import process crash because the special characters
during the conversion pass from one byte to two bytes:


IMP-00019: row Rejected due to Oracle error 12899
IMP-00003: ORACLE Error 12899 Encountered
ORA-12899: value too large for column "SCHEME". "TABLE". "ROW" (actual: 51, maximum: 50)


1- Export the schema you want to move:
exp system/password file=filename.dmp log=logfile.log owner=schemaname

2- Import only the table definitions into the new database, without inserting the rows (ROWS=N import)
imp system/password file=filename.dmp log=logfile.log rows=n fromuser=schemaname touser=schemaname

3- Converts columns to CHAR length semantics:
Follwoing query generate the scripts to change the CHAR length semantics

select 'ALTER TABLE ' || t.owner || '.' || c.table_name||' modify (' || c.column_name ||' '||c.data_type || '(' || c.char_length||' CHAR));'
from all_tab_columns C, all_tables T
where C.owner = T.owner
and T.owner in ('TEST1')
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');

After getting the scripts execute it on the new imported schema.  


4- Import the full schema with data now
imp system/password file=filename.dmp log=logfile.log fromuser=schemaname touser=schemaname