Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Saturday, 6 April 2013

Character Set Migration using CSSCAN Utility

Sometimes as per the requirement of application we need to change the database character set. As the character set conversion can cause data loss or data corruption, it is necessary before altering the character set, check the convertibility of the data.
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.
The scanner checks all the character set in the database including the data dictionary and test for the effects and problem of changing the character set encoding.
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: The CSALTER script does not perform any user data conversion. It only changes the character set metadata in the data dictionary. It is possible to run CSSCAN from a client, but this client needs to be the same base version as the database home. For example oracle 10g server need oracle 10g client.
Steps to change DB Characterset:
Step 1: Before starting character set conversion we need the following pre-checking for database.
  1. Remove the invalid objects
List all of schema which contains invalid objects. These invalid object need to be compiled or dropped, if they are unused
SQL> select distinct owner from dba_objects where status=’INVALID’;
SQL> exec utl_recomp.recomp_serial(‘SCHEMA’);
  1. Purge the Recyclebin
If there are object in recyclebin then purge the recyclebin otherwise during CSALTER you will get ora-38301.
  1. Take a full backup of the database.
Performing a backup before starting the characterset conversion is very important. If the conversion fails in middle you must restore from a backup before reattempting the conversion.
Step 2: Install the CSS utility. If the character set migration utility schema is not installed on your database. You will get the error: CSS-00107: Character set migration utility schema not installed. Install the CSS utility by running the csminst.sql script from $ORACLE_HOME\rdbms\admin.
Step 3: Run the Database Character Set Scanner utility as set the oracle_sid and run as
CSSCAN sys/password@instance_name AS SYSDBA FULL=Y
Step 4: Once the scan has completed successfully, the database should be opened in restricted mode so you can run the CSALTER script from $ORACLE_HOME\rdbms\admin folder. If there is any possible conversion problem, the process will report the problem and clean itself up without performing the conversion. Once the successfully conversion is completed you must restart the instance.
SQL>shutdown immediate;
SQL>startup restrict;
SQL>shutdown immediate;
Caution:  Changing the database character set is not an easy task. It is quite tricky tasks and may face errors which need the oracle support. So, I would strongly recommend, better to involve the oracle support.


  1. BlueHost is ultimately the best hosting provider with plans for any hosting requirements.