Define Character Sets for an Oracle Database

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

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

• NLS_CHARACTERSET → Controls the character set for CHAR, VARCHAR2, and CLOB.
• NLS_NCHAR_CHARACTERSET→ Controls the character set for NCHAR, NVARCHAR2, and NCLOB.

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: Specifies Unicode UTF-8 (recommended for international applications).
• NATIONAL CHARACTER SET AL16UTF16: Defines NCHAR, NVARCHAR2, and NCLOB storage.

1. Step 1: Shut Down the Database

         SHUTDOWN IMMEDIATE;
       

2. Step 2: Start in Restrict Mode

         STARTUP RESTRICT;
       

3. Step 3: Change the Character Set
   If changing to a superset (e.g., WE8ISO8859P1 → AL32UTF8), you can use:

         ALTER DATABASE CHARACTER SET AL32UTF8;
       

   If changing to a non-superset, use CSALTER Script:

         ALTER DATABASE CHARACTER SET INTERNAL_USE AL32UTF8;
       

   Warning: This method is riskier and should only be used if data integrity is ensured.
4. Step 4: Restart the Database

         SHUTDOWN IMMEDIATE;
         STARTUP;
       

1. Use Unicode (AL32UTF8) for applications that support multiple languages.
2. Ensure compatibility with existing applications before modifying the character set.
3. Backup the database before making any changes to the character set.
4. Check for data conversion issues using:
   

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

5. Validate the character set change using:
   

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