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.
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.
SQL> ALTER USER csmig QUOTA UNLIMITED ON tbs_csmig; User altered.
Checking that CSSCAN is working properly
Run CSSCAN using table SYSTEM.HELP as example.
bash$ csscan TABLE='(SYSTEM.HELP)' FROMCHAR=US7ASCII TOCHAR=US7ASCII LOG=instchkc CAPTURE=N PROCESS=1 ARRAY=1024000
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 126.96.36.199.0 - Production Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Username: / as sysdba Password: Connected to: Oracle Database 11g Enterprise Edition Release 188.8.131.52.0 - 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.