CLOBs and NCLOBs character set storage in Oracle Release 8i, 9i, 10g and higher (Doc ID 257772.1)

Purpose

CLOBs when using fixed width character set NLS_CHARACTERSET are stored in the NLS_CHARACTERSET character set on disk. Examples are WE8MSWIN1252 , AR8ISO8859P6 etc.

However , CLOB's (and NCLOB's) of varying-width character sets (ex: UTF8, AL32UTF8) are stored in an internal fixed-width Unicode character set on disk. When data is fetched from, or written to, disk storage, Oracle converts between the internal  format and the database (CLOB) or the national character (NCLOB) set Oracle uses for the "external" (N)CLOB datatypes.
This can sometimes lead to issues where it's unclear what is exactly being stored on disk. This note explains how this data is stored, and differences between different Oracle versions. Common varying-width database and national character sets are summarized in Table 1.

Table 1: Common varying-width charsets Oracle supports

Also refer to appendix A of Oracle Globalization Support Guide for a list of varying-width multibyte character sets.

Scope

This note is intended for anyone wishing to understand the internal storage of CLOB and NCLOB datatypes in varying-width character sets like UTF8 and AL32UTF8.

Details

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