How to configure an Oracle database for Unicode (AL32UTF8) character set

To configure the Oracle database to use Unicode, you need to select the AL32UTF8 character set when creating it, or use the migration tool to convert the existing database to AL32UTF8; if direct changes are not supported, you need to use Data Pump to export and import it to the new database.

To configure an Oracle database to use Unicode with the AL32UTF8 character set, you need to ensure that the database is created or migrated properly to support UTF-8 encoding. AL32UTF8 is Oracle's implementation of UTF-8 and supports all Unicode characters, making it ideal for multilingual applications.

Choose AL32UTF8 During Database Creation

If you're setting up a new Oracle database, the best approach is to specify AL32UTF8 at creation time.

• Use the Database Configuration Assistant (DBCA) and select "Advanced Mode" to access character set options.
• On the "Character Sets" screen, choose "Unicode" and then select AL32UTF8 as the database character set.
• Ensure the national character set is set to AL16UTF16 (recommended for NCHAR, NVARCHAR2 types).
• Proceed with database creation. Once set, the character set cannot be changed without migration.

Verify Current Character Set

For existing databases, check the current settings before making changes.

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

If NLS_CHARACTERSET is not AL32UTF8, the database isn't using Unicode by default for VARCHAR2 and CHAR columns.

Migrate Existing Database to AL32UTF8

Changing the character set after creation requires migration using Oracle's Character Set Migration Tool.

• Run the prerequisite script to assess migration feasibility:

 @?/rdbms/admin/cscheck.sql

 @?/rdbms/admin/csalter.plb

 ALTER DATABASE CHARACTER SET AL32UTF8;

Use Data Pump for Unsupported Migrations

If direct migration isn't possible, export and import the data using UTF-8.

• Export the database using expdp while the source database is running.
• Create a new database with AL32UTF8 character set.
• Import the data using impdp . Data will be automatically converted to UTF-8 during import.
• Test application behavior and validate text rendering, especially for non-ASCII content.

Proper Unicode support ensures your database can store and process text in any language. Setting AL32UTF8 early avoids complexity later. For production systems, test migration thoroughly in a staging environment.

Basically just plan ahead or follow the migration path carefully — once the database is live, changing the character set isn't trivial.

The above is the detailed content of How to configure an Oracle database for Unicode (AL32UTF8) character set. For more information, please follow other related articles on the PHP Chinese website!