Changing character set for a database

In this example, we are changing to UTF8 from AL32UTF8

Step 1: Check current character set.

SELECT value$ FROM sys.props$ WHERE name ='NLS_CHARACTERSET';

Step 2: Drop existing user CSMIG form database if exists.

drop user csmig cascade;

Step 3: Create new CSMIG user using below script.

@?/rdbms/admin/csminst.sql

Step 4: Check the objects impact if character set changed, we need to check looses.

csscan \"sys/sys as sysdba\" FULL=Y FROMCHAR=AL32UTF8 TOCHAR=UTF8 
LOG=dbcheck_utf82 CAPTURE=N ARRAY=1000000 PROCESS=6

Step 5: export the objects which are impacted in above step

Step 6: shutdown and start database in restricted mode.

shut immediate
startup restrict

Step 7: change character set using below script, it will take inputs from step 4.

@?/rdbms/admin/csalter.plb

Step 8: start the database in normal mode.

shut immediate 
startup

Step 9: check the character set after change.

SELECT value$ FROM sys.props$ WHERE name ='NLS_CHARACTERSET';