--------------------------Change Oracle DB Character Set---------------------------------------------
If you want to change from WE8MSWIN1252 to AL32UTF8. Check the NLS parameters first.
SQL> SELECT PARAMETER, VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER IN ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET');
---------------------------------------Changing Commands------------------------------------------------
The syntax of the ALTER DATABASE CHARACTER SET statement is as follows:
SQL> ALTER DATABASE [db_name] CHARACTER SET new_character_set;
(db_name is an optional)
For Example :
SQL> ALTER DATABASE CHARACTER SET AL32UTF8;
--------------------------------Steps to change the database character set------------------------------------------
To change the database character set, perform the following steps:
1. Shut down the database, using either a SHUTDOWN IMMEDIATE or a SHUTDOWN NORMAL statement.
2. Do a full backup of the database because the ALTER DATABASE CHARACTER SET statement cannot be rolled back.
3. Startup Oracle database
SQL> startup mount;
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
SQL> ALTER SYSTEM SET AQ_TM_PROCESSES=0;
SQL> ALTER DATABASE OPEN;
SQL> ALTER DATABASE CHARACTER SET AL32UTF8;
If you get the error ORA-12712, please update the following table.
'ORA-12712: new character set must be a superset of old character set'
SQL> update sys.props$ set VALUE$='AL32UTF8' where NAME='NLS_CHARACTERSET';
SQL> commit;
If you get the error ORA-12721, please login as DBA user.
'ORA-12721: operation cannot execute when other sessions are active'
4. shutdown immediate; or shutdown normal;
5. startup oracle database
SQL> startup mount;
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
SQL> ALTER SYSTEM SET AQ_TM_PROCESSES=0;
SQL> ALTER DATABASE OPEN;
SQL> ALTER DATABASE CHARACTER SET AL32UTF8;
SQL> shutdown immediate;
SQL> startup;
--------------------------------------Check the NLS parameters-------------------------------------------
SQL> SELECT PARAMETER, VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER IN ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET');