Change Database Character Set in Oracle 12c

Change Database Character Set in Oracle 12c

For the previous version, the official method for character set is using CSSCAN and ASALTER utility. So, from Oracle Database 12c, Oracle introduced a new tool called Database Migration Assistant for Unicode referred in Doc ID 1272374.1. But we have two cases; the first one is applied only if the new character set is a strict superset of the current character set. If not, you will have the “ORA-12712: new character set must be a superset of old character set” error. And then Oracle introduces the “INTERNAL_USE” option. The two option avoid to you to use a traditional method by using Export/Import or Data Pump Utility.

To change the Character set, you use the statement as follow:

ALTER DATABASE CHARACTER SET new_character_set;

E.g.: ALTER DATABASE CHARACTER SET WE8MSWIN1252;

Note: You must notice that this action cannot be rolled back, but you re apply the same method to back to the previous character set. However, it’s advised to do a full backup of your database before applying the change.

We want now to change the NLS_CHARACTERSET from WE8MSWIN1252 to AL32UTF8;

SQL> select * from NLS_DATABASE_PARAMETERS

  2  where parameter='NLS_CHARACTERSET';

PARAMETER                      VALUE

----------------------------- ----------------------------------------

NLS_CHARACTERSET               WE8MSWIN1252

To change the database character set, perform the following steps:

1.       Properly Shut down the database:

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

2.       Start the Database as restricted session

SQL> startup restrict

ORACLE instance started.

Total System Global Area 5167382528 bytes

Fixed Size                  3842808 bytes

Variable Size            1090522376 bytes

Database Buffers         4060086272 bytes

Redo Buffers               12931072 bytes

Database mounted.

Database opened.

3.       Change the Character Set

SQL> alter database character set AL32UTF8;

alter database character set AL32UTF8

*

ERROR at line 1:

ORA-12712: new character set must be a superset of old character set

Don’t worry, in our case, the new Character Set is not a superset of the old one. In this case we must use the below:

SQL> alter database character set INTERNAL_USE AL32UTF8;

Database altered.

4.       Once the Character Set is changed, you must properly restart the database.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 5167382528 bytes

Fixed Size                  3842808 bytes

Variable Size            1090522376 bytes

Database Buffers         4060086272 bytes

Redo Buffers               12931072 bytes

Database mounted.

Database opened.

5.       Verify that the new Character set is applied

SQL> select * from NLS_DATABASE_PARAMETERS

  2  where parameter='NLS_CHARACTERSET';

PARAMETER

--------------------------------------------------------------------------------

VALUE

--------------------------------------------------------------------------------

NLS_CHARACTERSET

AL32UTF8

ENJOY.