How to change the character set of a database

Step 1 : Find the present character set of your database ..

SQL> select * from NLS_DATABASE_PARAMETERS where parameter in ('NLS_CHARACTERSET','NLS_NCHAR_CHARACTERSET');

PARAMETER VALUE

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

NLS_CHARACTERSET US7ASCII

NLS_NCHAR_CHARACTERSET AL16UTF16

Step 2 : How to change

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;
ALTER DATABASE OPEN;
select value from nls_database_parameters where parameter='NLS_CHARACTERSET';
---- TRUNCATE TABLE SYS.METASTYLESHEET; ---to get rid of ORA-12716 per Metalink Note - 213015.1
ALTER DATABASE CHARACTER SET UTF8;
SHUTDOWN IMMEDIATE;
STARTUP;
@$ORACLE_HOME/rdbms/admin/catmet.sql
SHUTDOWN IMMEDIATE;
STARTUP;


Step 3 : Now confirm the new character set of your database ..

SQL> select * from NLS_DATABASE_PARAMETERS where parameter in ('NLS_CHARACTERSET','NLS_NCHAR_CHARACTERSET');

PARAMETER VALUE

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

NLS_CHARACTERSET UTF8

NLS_NCHAR_CHARACTERSET AL16UTF16