Designing and setting up a new database system can take a lot of planning. But typically, one important decision is overlooked: choosing the right character set. A basic consideration for choosing a character set is to make sure it can handle any language that needs to be supported immediately and in the future. Oracle has documentation on the capabilities and performance implications of different character sets (Choosing a Character Set). Sometimes you have to change the database character set to support a new application.

The Character Set Scanner (CSSCAN) provides an assessment of the feasibility and potential issues in migrating an Oracle database to a new database character set.

What is CSSCAN?

CSSCAN is a scanning tool that allows you to see the impact of a database character set change or assist you to correct an incorrect database NLS_CHARACTERSET setup. It’s possible to run CSSCAN from a client, but this client needs to be the same base version as the database home. CSSCAN is by default included in any server or client installation in 10.1 and higher and is located in $ORACLE_HOME/bin.

Installing CSSCAN

CSSCAN stores internal data in the database when running, the schema for the CSSCAN tables is called CSMIG and is created using the csminst.sql script found in $ORACLE_HOME/rdbms/admin. In order to avoid problems, run CSSCAN from the database home using a local connection.

Installing CSSCAN in Oracle RDBMS 11.2

Note: that the default tablespace is SYSTEM, but it is strongly recommended to create a separate tablespace and change the $ORACLE_HOME/rdbms/admin/csminst.sql script to use this tablespace. Modify the following statement in csminst.sql to assign your preferred tablespace for the CSMIG schema instead of SYSTEM:

alter user csmig default tablespace SYSTEM quota unlimited on SYSTEM;

For the purpose of this blog the TBS_CSMIG tablespace will be used.

alter user csmig default tablespace TBS_CSMIG quota unlimited on TBS_CSMIG;

The amount of space needed depends mainly on the amount of exceptions found (convertible and/or lossy data) and is difficult to predict upfront. It’s useful to use on the first run of CSSCAN a table that is limited in size to avoid any disk space issues.

Run csminst.sql using sqlplus of the server home as follows:

bash$ sqlplus / as sysdba
SQL> set term out on echo on
SQL> spool csminst.log
SQL> @?/rdbms/admin/csminst.sql

Check the csminst.log for errors. The CSMIG user is needs quota on the TBS_CSMIG tablespace.


User altered.

Checking that CSSCAN is working properly

Run CSSCAN using table SYSTEM.HELP as example.

bash$ csscan TABLE='(SYSTEM.HELP)' 



This is just a test run to check if CSSCAN is working. There should be three files created from this command: instchkc.out, instchkc.txt and instchkc.err. The instchkc.out should look like this:

Character Set Scanner v2.2 : Release - Production

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba


Connected to:
Oracle Database 11g Enterprise Edition Release - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

Enumerating table to scan...

. process 1 scanning SYSTEM.HELP[AAADedAABAAAIWAAAA]

Creating Database Scan Summary Report...

Creating Individual Exception Report...

Scanner terminated successfully.

The “Scanner terminated successfully.” message indicates that everything is installed properly, and CSSCAN is now ready for usage. A future post will address some practical examples to illustrate the CSSCAN functionality.

Link to script that contains the examples in this post.