Change Database Character Set using CSSCAN

CSSCAN (Database Character Set Scanner) is a SCAN tool that allows us to see the impact of a database character set change or assist us to correct an incorrect database nls_characterset setup. 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.

Before altering the character set of a database, check the convertibility of the data before converting. Character set conversions can cause data loss or data corruption. The Character Set Scanner utility provides the below two  features: 

1.) Convertibility check of existing data and potential issues. The Scanner checks all character data in the database including the data dictionary and tests for the effects and problems of changing the character set encoding (characterset). At the end of the scan, it generates a summary and exception report of the database scan.

2.) Csscan allows  also us to do a check if there is no data in the database that is  incorrectly stored.

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. Each and every character in the current character set is available in the new character set and has the same code point value in the new character set.

With the strict superset criteria in mind, only the metadata is converted to the new character set by the CSALTER script, with the following exception: the CSALTER script performs data conversion only on CLOB columns in the data dictionary and sample schemas that have been created by Oracle. CLOB columns that users have created may need to be handled separately

Note :  it's possible to run Csscan from a client, but this client needs to be the same base version as the database home.(i.e, oracle 11g server need oracle 11g client) .

To change the database character set, perform the following steps :


STEP 1 : Remove the invalid objects and purge the recyclebin , then take a  full backup of the database.

STEP 2 : Install the CSS utility if not install . We will get error css-00107 if css utility is not install .Install the CSS utility by running the csminst.sql script which is found in $ORACLE_HOME\rdbms\admin .

STEP 3 : Run the Database Character Set Scanner utility as
set the oracle_sid and run as
CCSSCAN  /AS SYSDBA  FULL=Y


STEP 4 : Run the CSALTER script.This script is in $ORACLE_HOME\rdbms\admin  folder .
i.>   shut down 
ii.>  startup restrict 
iii.> @csalter.plb
iv.> shut immediate
v.>  startup  

Note :  

i.> The CSALTER script does not perform any user data conversion. It only changes the character set metadata in the data dictionary.Thus, after the CSALTER operation, Oracle will behave as if the database was created using the new character set.

ii.> Changing the database characterset is not an easy task . It is quite tricky tasks and may face errors which need the oracle support . So,it's better to raise as SR for this task and involve the  oracle support .  

There  are no of links on meta-link .Check the below meta-links .

Note: 555823.1 – Changing US7ASCII or WE8ISO8859P1 to WE8MSWIN1252
Note: 444701.1 – Csscan output explained
Note: 745809.1 – Installing and configuring Csscan in 10g and 11g (Database Character Set Scanner)
Note: 225912.1 – Changing the Database Character Set ( NLS_CHARACTERSET ) [ID 225912.1]




Enjoy   J J J