VARCHAR2 column length in a Multi Byte Characterset Database

When migrating data from a single-byte characterset database into a multi-byte characterset database, you will very likely encounter problems with column lenghts.

Oracle uses by default BYTE length sematics for VARCHAR2 columns.

As many characters need more than one byte in a multi-byte characterset database, the field lengths in BYTE will not be sufficient in many cases.

It will therefore be preferable in most cases to use CHAR length semantics in your multi-byte characterset database. This can be configured either on system or session level:

ALTER SYSTEM SET NLS_LENGTH_SEMANTICS=CHAR;
ALTER SESSION SET NLS_LENGTH_SEMANTICS=CHAR;

This way, you do not have to specify CHAR in each and every VARCHAR2 column definition.