Globalization

How to Change Database Character Set

Change Database Character Set

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

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    AL16UTF16

Please shutdown the database.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

Startup the database with restricted mode. No one can access the database except SYSDBA.

SQL> startup restrict
ORACLE instance started.
...
Database mounted.
Database opened.

Change the character set with internal_use.

SQL> alter database character set internal_use AL32UTF8;

Database altered.

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

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. Required fields are marked *