Database character set conversion checking using CSSCAN

by / 2010-2011 / Published in Oracle DBA Tips

remote oracle dba

CSSCAN is the best utility to determine the impact of migrating or converting Oracle database in to new Character Set. Using CSSCAN we can scan the data in the database and generate report that lists any issues involves in changing NLS character set of database. This most powerful utility for tracking any problem and issue in database character set migration with database support.

It is major requirement while you are working as expert remote dba with global clients and databases are used by different end users globally from anywhere of world.

What are options available with CSSCAN utility of Oracle?

Oracle DBA Interview Questions

CSSCAN utility provides 3 options for scanning; one option is scanning full database, using second option we can scan schema level and third option we can table level. But best option is scanning full database level because we are targeting to covert NLS character set of database. If size of database is large then we can schedule the CSSCAN with schema level or table level with partitioning or splitting our scanning task in different scripts and output reports. It is easiest way to scan large database using CSSCAN utility of Oracle database.

How to use CSSCAN before database character set conversion?

We need to create CSMIG before start performing scan of database using CSSCAN. For creating CSMIG user in database, we need to connect as SYS as SYSDBA into database and execute CSMINST.SQL script from $ORACLE_HOME/rdbms/admin folder.

SQL>connect SYS as SYSDBA

This script will create CSMIG user in SYSTEM tablespace, we need to allocate another default tablespace before start scanning of database.

Example of CSSCAN command execution:

D:\>csscan system/itmanager table=scott.dept fromchar=WE8MSWIN1252 tochar=UTF8 log=scott_dept_csscan.log

Using parameter process, we can parallelize our process also. Default is 1 process. If we use ARRAY parameter then we can provide buffer size of row fetching. Default size of ARRAY is 102400 bytes. These parameters are very useful to scan large data of large database.

There is EXCLUDE parameter also available for excluding any of tables. For checking more parameters and default setting, we should need to execute CSSCAN command with help=y parameter.

Example of CSSCAN command execution with parameter file:

We can use parameter file for CSSCAN utility like export and import parameter file. In parameter file we can provide such parameters like username, password, fromchar, tochar, log, etc.

Our parfile full_scan_to_utf8.txt may contain following parameters.

Table= scott.dept

In remote dba services, these type of Oracle errors are very critical to manage if you haven't access of some commands or not having more knowledge of operating system commands. Expert remote dba team of Dbametrix offers ultimate remote dba work with high availability and lowest cost remote plans.

Dbametrix is world wide leader in remote dba support. Expert remote DBA team of Dbametrix is offering high quality professional Oracle DBA support with strong response time to fulfill your SLA. Contact our sales department for more information.