Friday, 25 October 2013

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

No comments:

Post a Comment

Number of Visitors