Change Oracle DB Character Set

--------------------------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');