CSSCAN character set scanner utilities (By Gurmeet Singh)

CSSCAN character set scanner utilities



Basically 2 type of character set are available in Oracle. Single byte character sets and Multi-bytes character sets. Whenever we will perform character set migration from single bytes character sets to Multi-bytes character set then depending on the data size truncation can be occurred. For that case we need to increase the size of those columns. To find all those culprit columns we can use CSSCAN utility.

CSSCAN Utility Usage:

First of all we need to install CSSCAN utility. If CSSCAN utility is not installed in database the it will give following error.
C:\>
C:\>CSSCAN \"sys/*****@orcl as sysdba\" FULL=Y

Character Set Scanner v2.0 : Release 10.1.0.4.0 - Production on Wed Feb 24 13:53:44 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Production
With the Partitioning, OLAP and Data Mining options
CSS-00107: Character set migration utility schema not installed
Scanner terminated unsuccessfully.
C:\>



If CSMIG schema is not installed then we have to run the "ORACLE_HOME/rdbms/admin/csminst.sql" as sys in sql*plus.


Character Set Scanner v2.0 : Release 10.1.0.4.0 - Production on Wed Feb 24 14:06:57 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Production
With the Partitioning, OLAP and Data Mining options
Current database character set is AL32UTF8.
Enter new database character set name: > WE8ISO8859P1
Enter array fetch buffer size: 102400 >
Enter number of scan processes to utilize(1..32): 1 > 32

Enumerating tables to scan...

. process 1 scanning SYS.SOURCE$[AAAABHAABAAAP6JAAA]

. process 8 scanning SYS.TAB$[AAAAACAABAAAAAZAAA]

. process 22 scanning SYS.CLU$[AAAAACAABAAAAAZAAA]

. process 24 scanning SYS.OPQTYPE$[AAAAACAABAAAAAZAAA]

. process 5 scanning SYS.ICOL$[AAAAACAABAAAAAZAAA]

. process 1 scanning SYS.LIBRARY$[AAAAACAABAAAAAZAAA]

..

..

..

..

. process 9 scanning SYS.VIEW$[AAAAA+AABAAAAHBAAA]

. process 14 scanning WK_TEST.DR$WK$DOC_PATH_IDX$I[AAAPUOAADAAAE2JAAA]

. process 17 scanning SYSMAN.MGMT_E2E_JDBC_1HOUR[AAAP2JAADAAAFPxAAA]

. process 3 scanning SYSMAN.MGMT_E2E_JDBC_1DAY[AAAP2KAADAAAFP5AAA]

. process 1 scanning SYSMAN.MGMT_BLACKOUTS[AAAP2QAADAAAFQpAAA]

. process 10 scanning SYS.USER$[AAAAAKAABAAAABZAAA]





Creating Database Scan Summary Report...

Creating Individual Exception Report...

Scanner terminated successfully.



While scanning utility will create 32 session as we have provide value 32 for "Enter number of scan processes to utilize(1..32): 1 >" parameter.

As
SQL> SELECT COUNT(*) FROM V$SESSION WHERE PROGRAM = 'csscan.exe';
COUNT(*)
----------
32



After completing the scan it will created 3 different files as out in file system.
Scan.txt : Database Scan Summary Report
Scan.err : Database Scan Individual Exception Report (It will contain all rows which will be truncated with its table name, cell data and rowids )
Scan.out : Scan log


CSALTER Utility to alter Database Character set

Basically two methods are available for migrating character set.
- Using Import and Export Utilities
- Using CSALTER Utility

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. The new character set is a strict superset of the current character set i.e. Each and every character in the current character set is available in the new character set.



CSALTER Utility will only handle CLOBs which are a part of Data Dictionary and will not handle user created CLOB.

For CSALTER Utility database Instance should be opend as restricted mode and "CLUSTER_DATABASE=FALSE" i.e. only open instance should be up in case of RAC.



SQL> SHUTDOWN IMMEDIATE

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> STARTUP RESTRICT

ORACLE instance started.



Total System Global Area 289406976 bytes

Fixed Size 1248600 bytes

Variable Size 83886760 bytes

Database Buffers 201326592 bytes

Redo Buffers 2945024 bytes

Database mounted.

Database opened.



SQL> @@csalter.plb



0 rows created.



Function created.



Function created.



Procedure created.





This script will update the content of the Oracle Data Dictionary.

Please ensure you have a full backup before initiating this procedure.

Would you like to proceed (Y/N)?Y



old 6: if (UPPER('&conf') <> 'Y') then

new 6: if (UPPER('Y') <> 'Y') then



Checking data validility...



begin converting system objects



PL/SQL procedure successfully completed.



Alter the database character set...



CSALTER operation completed, please restart database



PL/SQL procedure successfully completed.



0 rows deleted.



Function dropped.



Function dropped.



Procedure dropped.



SQL>



If there will be any conversion problems then the process will report the problem and clean itself up without performing the conversion.