Lesson 4	Character Sets
Objective	Understand how to define a Character Set for a Database

Define Character Sets for an Oracle Database

The previous lesson covered NLS_LANG — the client-side setting that shapes how your session behaves once you connect. A database's character set is a different animal entirely: it's a server-side property fixed at database creation, and it determines how character data is physically encoded and stored, for every session, regardless of what any client sets. Where NLS_LANG is something a user chooses, the character set is something an administrator decides once, permanently, on behalf of everyone who will ever connect.

This matters because the character set governs what languages and symbols your database can represent at all. Get it wrong, and no amount of client-side tuning can fix data that was never stored correctly in the first place.

Checking the Current Character Set

Before changing or defining a character set, you can check what your database is currently using:


SELECT parameter, value
FROM nls_database_parameters
WHERE parameter IN ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET');

Example Output:


PARAMETER               VALUE
-------------------     -------------------
NLS_CHARACTERSET        AL32UTF8
NLS_NCHAR_CHARACTERSET  AL16UTF16

NLS_CHARACTERSET → the character set for CHAR, VARCHAR2, CLOB, and LONG columns, plus identifiers and stored SQL/PL/SQL source.
NLS_NCHAR_CHARACTERSET → the separate national character set used for NCHAR, NVARCHAR2, and NCLOB columns.

Defining a Character Set at Database Creation

In Oracle AI Database 26ai, you specify the character set in the CREATE DATABASE statement, through the Database Configuration Assistant (DBCA), or via other installation tooling — but you must specify one, and you'll live with the consequences of that choice indefinitely.
Example: Creating a Database with Unicode


CREATE DATABASE mydb
USER SYS IDENTIFIED BY mypassword
USER SYSTEM IDENTIFIED BY mypassword
LOGFILE GROUP 1 ('/oracle/oradata/mydb/redo01.log') SIZE 50M,
        GROUP 2 ('/oracle/oradata/mydb/redo02.log') SIZE 50M,
        GROUP 3 ('/oracle/oradata/mydb/redo03.log') SIZE 50M
MAXLOGFILES 5
DATAFILE '/oracle/oradata/mydb/system01.dbf' SIZE 700M AUTOEXTEND ON
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16;

CHARACTER SET AL32UTF8 — Unicode UTF-8, the default and strongly recommended choice for new Oracle 26ai databases. It supports virtually every language in active use and is the industry-standard encoding.
NATIONAL CHARACTER SET AL16UTF16 — governs NCHAR, NVARCHAR2, and NCLOB storage specifically. Most databases never touch this beyond accepting the default, but it exists as a second, independent character-set choice layered on top of the primary one.

Non-Unicode character sets are still supported and documented in the Oracle AI Database Globalization Support Guide, but they're discouraged for new deployments — they cover fewer languages and create migration debt down the road if the database ever needs to support content it wasn't originally built for.

What Is an Encoded Character Set?

Choosing a character set determines what languages the database can represent, and it quietly affects more than storage:

How you design the schema
How applications process character data
How the database interacts with the operating system
Performance

A character set works by assigning a unique numeric code — a code point — to every character in its repertoire: letters, digits, punctuation, control characters, and for larger sets, ideographs and symbols from dozens of writing systems. When the database stores the letter A, it isn't storing a picture of the letter; it's storing the numeric code that software knows to interpret and render as "A". The table below shows a handful of code-point assignments from the ASCII character set, purely as an illustration of the concept — ASCII is a tiny, single-byte set covering basic English; it's nowhere near the scale of a multi-byte, multilingual set like AL32UTF8, which is exactly why Unicode became the default rather than staying a per-language choice.

Example Code Points in the ASCII Character Set

Character	Description	Code Value
!	Exclamation Mark	21
#	Number Sign	23
$	Dollar Sign	24
1	Number 1	31
2	Number 2	32
3	Number 3	33
A	Uppercase A	41
B	Uppercase B	42
C	Uppercase C	43
a	Lowercase a	61
b	Lowercase b	62
c	Lowercase c	63

Oracle supports both single-byte character sets (sufficient for English and many Western European languages) and multi-byte sets (required for languages like Chinese, Japanese, and Korean, which have far more characters than a single byte can address). Rather than juggling a different single-byte set per language, Oracle's Unicode-based sets encode the vast majority of the world's writing systems within one scheme — which is the practical reason AL32UTF8 is the default rather than one legacy option among many.

The National Character Set

Alongside the primary database character set, every Oracle database also has a national character set, set independently via the NATIONAL CHARACTER SET clause shown above. It exists specifically for the NCHAR, NVARCHAR2, and NCLOB data types — a parallel storage path originally intended for cases where an application needed fixed-width Unicode support distinct from the main character set. In practice, most modern schemas standardize on AL32UTF8 for everything and rarely lean on the national character set's separate behavior, but it's worth knowing it's there: if you ever see NCHAR or NVARCHAR2 columns behaving differently than their CHAR/VARCHAR2 counterparts, this is why.

Character Sets in a Multitenant Environment

If you're working in a multitenant architecture — a container database (CDB) hosting one or more pluggable databases (PDBs) — the character set decision happens at the CDB level and has consequences for every PDB underneath it. Setting the CDB's character set to AL32UTF8 gives you the most flexibility when plugging in PDBs later, since Unicode can accommodate PDBs that were themselves created with a variety of character sets, with some exceptions around EBCDIC-based sets. Choosing a narrower character set for the CDB constrains what PDBs can be plugged in without a conversion step, so this is one more reason the Unicode default is the safer starting point rather than an afterthought.

For Oracle Autonomous Database, character set selection is likewise supported for most workload types, again defaulting to and generally recommending AL32UTF8, though specific restrictions can apply depending on the workload (transaction processing, data warehousing, and so on).

Changing the Character Set of an Existing Database

You define a character set when you create the database, and in practice, you should treat that decision as permanent. Changing it later is possible but risky, disruptive, and only worth attempting when absolutely necessary — done carelessly, it can corrupt existing data. If a change is genuinely required, the process looks like this:

Shut Down the Database
```
SHUTDOWN IMMEDIATE;
```
Start in Restricted Mode
```
STARTUP RESTRICT;
```
Change the Character Set
If you're changing to a superset of the current character set (for example, WE8ISO8859P1 → AL32UTF8), a direct ALTER DATABASE is sufficient:
```
ALTER DATABASE CHARACTER SET AL32UTF8;
```
If you're changing to a non-superset — a set that isn't a strict superset of the existing one — that direct statement isn't safe, and you need the CSALTER script instead:
```
ALTER DATABASE CHARACTER SET INTERNAL_USE AL32UTF8;
```
This path is riskier and should only be used once you're confident in the data's integrity and have a verified backup.
Restart the Database
```
SHUTDOWN IMMEDIATE;
STARTUP;
```

If you're provisioning through DBCA rather than scripting `CREATE DATABASE` by hand, the character set is chosen in the Database Creation Wizard's Character Set step — same decision, different interface.

Best Practices for Choosing a Character Set

Default to Unicode (AL32UTF8) unless you have a specific, well-understood reason not to — it covers the broadest range of languages and avoids painting yourself into a corner.
Confirm compatibility with existing applications before modifying a character set on a live system.
Take a verified backup before making any character-set change.

Check for potential data conversion issues ahead of a change:


SELECT column_name, char_length, char_used
FROM user_tab_columns
WHERE data_type IN ('CHAR', 'VARCHAR2', 'CLOB');

Validate the result after a change:


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

Character sets are one of the few database decisions that are genuinely difficult to undo. Get Unicode right at creation time, and everything downstream — NLS parameters, session behavior, application encoding — has a solid foundation to build on. In the next lesson, we'll look at how to actually work with the character set you've defined.