How can I tell if my Oracle system is set to support Unicode or multibyte characters?

Question

I understand that Oracle supports multiple character sets, but how can determine if the current 11g system where I work has that functionality enabled?

Facile

Answer 1

SELECT *
  FROM v$nls_parameters
 WHERE parameter LIKE '%CHARACTERSET';

will show you the database and national character set. The database character set controls the encoding of data in CHAR and VARCHAR2 columns. If the database supports Unicode in those columns, the database character set should be AL32UTF8 (or UTF8 in some rare cases). The national character set controls the encoding of data in NCHAR and NVARCHAR2 columns. If the database character set does not support Unicode, you may be able to store Unicode data in columns with these data types but that generally adds complexity to the system-- applications may have to change to support the national character set.

Webber

Answer 2

Unicode is a character encoding system that defines every character in most of the spoken languages in the world, Support for Unicode in Oracle Database:

Character Set   Supported in RDBMS Release  Unicode Encoding
AL24UTFFSS      7.2 - 8i                    UTF-8
UTF8            8.0 - 11g                   UTF-8
UTFE            8.0 - 11g                   UTF-EBCDIC
AL32UTF8        9i - 11g                    UTF-8
AL16UTF16       9i - 11g                    UTF-16

To Make sure your database is Unicode, please check the value of "NLS_CHARACTERSET" Parameter and it should be AL32UTF8 or AL16UTF16 from above list.

SQL>
SQL> SELECT * FROM v$nls_parameters WHERE parameter='NLS_CHARACTERSET';

PARAMETER                   VALUE               CON_ID
--------------------------- ------------------- ----------
NLS_CHARACTERSET            AL32UTF8            0

To Change the value of Parameter, Please Take the Fullback up because ALTER DATABASE statement cannot be rolled back and the Use following statements:

SHUTDOWN IMMEDIATE
STARTUP MOUNT;
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;
ALTER DATABASE OPEN;
ALTER DATABASE CHARACTER SET AL32UTF8;
SHUTDOWN IMMEDIATE;
STARTUP;

Per

Command ALTER DATABASE CHARACTER SET ... is de-supported since Oracle 10R1, you may destroy your database (see example #15224515). However, if the old character set is US7ASCII then this approach may work exceptionally. – Cullie

Answer 3

The Rajesh's answer is almost exact. I was taken interest of AL24UTFFSS charset. A old Oracle 7.2 documentation (A20327-2, Oracle7 Server Reference, page 3-31) is stated:

The character set name for UTF2 is AL24UTFFSS. Conversion between UTF2 and other existing character sets is provided in this release of Oracle Server. Conversion between UTF2 and singlebyte character sets is done through an internal number matching mechanism; conversion between UTF2 and multibyte character sets is done with conversion functions and tables.

Clients should be aware that AL24UTFFSS is now officially supported as a new character set. Since the encoding scheme of UTF2 is very similar to some existing character sets, no major impact on existing products is expected.

But Rajesh said: AL24UTFFSS is UTF8. I know that UCS2 is obsolete term for UTF16 (i.e. AL16UTF16). Personally I is not acquainted with "UTF2" term, but "AL24" prefix hints at some 3-byte charset. It not looks like UTF8.

Boutin