ORA-02374, ORA-12899, ORA-02372 during Data Pump expdp/impdp from 12c WE8MSWIN1252 into 12c AL32UTF8

PROBLEM

During a data pump expdp/impdp from 12c WE8MSWIN1252 into 12c AL32UTF8, I received several errors in the data pump import log file:

07-JUN-18 17:00:12.953: ORA-02374: conversion error loading table "XXX"."ABC"
07-JUN-18 17:00:12.953: ORA-12899: value too large for column ABC_NDC (actual: 22, maximum: 11)
07-JUN-18 17:00:12.953: ORA-02372: data for row: ABC_NDC : 0X'9F9F9F9F9F9F9F9F9F9F9F'


07-JUN-18 22:46:02.663: KUP-11007: conversion error loading table "XXX"."ABC"
07-JUN-18 22:46:02.663: ORA-12899: value too large for column ABC_TOB (actual: 5, maximum: 4)
07-JUN-18 22:46:02.663: KUP-11009: data for row: ABC_TOB : 0X'303136A0'

CAUSE

The cause of these errors is related to the fact that I am migrating data from a database with a single-byte character set (WE8MSWIN1252) into one with a multi-byte character set (AL32UTF8). This means that some single-byte characters will be “expanded” into multi-byte characters, and if the column was already filled completely, ORA-12899 will be raised, showing the maximum allowed column value and the actual value needed to fit all the characters into the multi-byte column.

There are several solutions to this problem: increase the size of the source column or truncate the data before the import, stick to the same character set, pre-create the tables with modified column sizes in the source database before the import

If you have a lot of rows with conversion errors in your import log file, there’s another solution. This requires the installation of the Database Character Set Scanner utility (csscan) into your source database.

Csscan is installed by running the csminst.sql script under $ORACLE_HOME/rdbms/admin. This script will create a user “csmig”. It’s a good idea to first modify the following line so the user csmig doesn’t write into the SYSTEM tablespace:

alter user csmig default tablespace SYSTEM quota unlimited on SYSTEM

In my case, I replaced SYSTEM by SYSAUX.

Let’s install csscan into our source database:

$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus /nolog

SQL*Plus: Release 12.2.0.1.0 Production on Fri Jun 8 11:28:41 2018

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

SQL> conn / as sysdba
Connected.
SQL> set TERMOUT ON
SQL> set ECHO ON
SQL> spool csminst.log
SQL> @csminst.sql

After this you should check the csminst.log file for errors. 

Now that csscan is installed, you can use it to check specific schemas or tables for data conversion problems during the migration to another character set. I executed it as follows (you will need the password of the SYS user):

$ csscan \”sys as sysdba\” LOG=/tmp/csscan.log USER=XXX CAPTURE=Y TOCHAR=AL32UTF8 ARRAY=1024000 PROCESS=3

This will analyze all character data stored in the tables of the schema "VSVW2LO112_1801". The script will create 3 log files. The log file with the extension “.err” has the list of all affected rows with their corresponding ROWID’s. You can count the affected rows from the log file using the UNIX “grep” command:

$ grep -i “exceed column size” /tmp/csscan.log.err|wc -l

38

So, in my case, 38 rows from the "" schema will have conversion problems during the migration to the AL32UTF8 character set.

You can remove csscan by dropping the user “csmig”. More information regarding csscan can be found on My Oracle Support, please see document ID 1297961.1.

select VALUE, ISDEFAULT   from v$parameter   where NAME='nls_length_semantics';
VALUE           ISDEFAULT
--------------- --------------------
BYTE            TRUE

select VALUE   from nls_database_parameters   where parameter='NLS_CHARACTERSET';
VALUE
---------------

WE8MSWIN1252