Migrating the character set

As per requirement of application,We can change character set on existing database.

CSSCAN Utility and CSALTER Script:

It is two stage process: data scanning (csscan) and data conversion (csalter.plb).

The CSSCAN Utility is used to denitrify possible database character set conversion problems and truncation of data. Data scanning identifies the amount of effort required to migrate data into the new character encoding scheme before changing the database character set. This information helps to determine the best approach for converting the database character set.

The CSALTER script is part of the Database Character Set Scanner utility. The CSALTER script is the most straightforward way to migrate a character set, but it can be used only if all of the schema data is a strict subset of the new character set. The new character set is a strict superset of the current character set if:
Process steps

Database Version: Oracle 10g Database

Source Character Set: WE8ISO8859P1
Target Character Set: AL32UTF8

1)Shut down the database,.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
2)Take a full database backup:
performed the full database export using Datapump utility (expdp/impdp)

3)Start up the database:
SQL> startup;

4)Run the Database Character Set Scanner (CSSCAN) utility.

This CSSCAN Utility executable is under the $ORACLE_HOME/bin directory.
$ csscan \"sys/password@mydb as sysdba\" full=y

During this activity script asks enter the new database character set name please enter the name

Enter new database character set name: > AL32UTF8

for any help on CSSCAN you can use

$ csscan help=y

Run the CSALTER script.

After Database Character Set Scanning has completed successfully, the database must be opened in restricted mode, because no normal user should allow to access the database during this task is being performed. So you can run the CSALTER script as the SYS user. The location of the CSALTER Script is "$ORACLE_HOME/rdbms/admin/csalter.plb".

Shut Down the Database
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

Start Up the Database in Restricted Mode.
SQL> startup restrict;

Run the csalter.plb script
SQL> @?/rdbms/admin/csalter.plb

Shut Down the Database
SQL> shutdown immediate;

Start Up the Database
SQL> startup;
SQL>
Make sure the changes:
SQL> SELECT * FROM nls_database_parameters;