Globalization

How to Change Database Character Set

Assuming that you have exported all the data to dump file for further process. Please shutdown the database.
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

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

Startup the database with restricted mode.
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.

Leave a Reply

Your email address will not be published. Required fields are marked *