My Oracle Support Banner

Examples and limits of BYTE and CHAR semantics usage (NLS_LENGTH_SEMANTICS) (Doc ID 144808.1)

Last updated on JUNE 13, 2023

Applies to:

Oracle Database Backup Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Oracle Database - Enterprise Edition - Version to [Release 9.0.1 to 12.2]
Oracle Database - Standard Edition - Version and later
Oracle Database Cloud Schema Service - Version N/A and later
Information in this document applies to any platform.


To document best practices, limits and details when implementing NLS_LENGTH_SEMANTICS as BYTE or CHAR.


Any DBA wanting to use NLS_LENGTH_SEMANTICS.

NLS_LENGTH_SEMANTICS allows you to specify the length of a column datatype in terms of CHARacters rather than in terms of BYTEs. Typically this is when using an AL32UTF8 or other varying width NLS_CHARACTERSET database where one character is not always one byte. While using CHAR semantics has as such no added value in a 7/8 bit characterset it's fully supported so any application code / table setup using CHAR can also be used in a 7/8bit characterset like US7ASCII/WE8MSWIN1252.
This parameter is a 9i (and up) feature and is not available in older releases.

Make sure to check also "Things to know when using CHAR semantics:" as there are certain limitations on when you can use this.


To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document
 B. Defaults and limits.
 C. NLS_LENGTH_SEMANTICS has no effect on tables owned by SYS.
 D. Anonymous blocks will NOT pick up changed NLS_LENGTH_SEMANTICS parameter.
 E. How to go to CHAR semantics for existing tables?
 E.1) Using exp/imp or expdp/impdp.
 E.2) Use alter table modify.
 F. Can i use CHAR semantics for TYPES?
 G. Summary of best practices:
 H. More Examples
 I. Known problems:

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.