Skip to content

How Oracle Change Character Set

  • by

Assuming that you have exported all the data to dump files for later processes.

Database Character Set

Check current setting on character sets.

SQL> column parameter format a25;
SQL> column value format a25;
SQL> select parameter, value from v$nls_parameters where parameter like '%CHARACTERSET%';

PARAMETER                 VALUE
------------------------- -------------------------
NLS_CHARACTERSET          ZHT16MSWIN950
NLS_NCHAR_CHARACTERSET    UTF8

1. Enable Restricted Mode

We should enable the restricted mode for further operation.

2. Check Restricted Mode

Make sure the database is in restricted mode.

SQL> select logins from v$instance;

LOGINS
----------
RESTRICTED

3. Change Character Set

NLS_CHARACTERSET

The character set you choose to change should be suitable for your application, in this case, we choose AL32UTF8. Below we change the character set with internal_use.

SQL> alter database character set internal_use AL32UTF8;

Database altered.

NLS_NCHAR_CHARACTERSET

For national character set of a database, we can do this:

SQL> alter database national character set internal_use AL16UTF16;

Database altered.

4. Start DB

Bounce the database normally.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
...
Database mounted.
Database opened.
SQL> select parameter, value from v$nls_parameters where parameter like '%CHARACTERSET%';

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

There could be more considerations about changing the character set of a database.

After that, you can import the data back to this database by data pump. If there's any error during importing, you should analyze and take care of it. Mostly, you need to expand column width in order to accommodate more bytes.

Is there any chance that we don't have to converting data? You may check the post: Can I Change Current Character Set into AL32UTF8 without Converting Data?

Leave a Reply

Your email address will not be published.