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

No comments:

Powered by Blogger.