3.8 使用 csscan 辅助字符集转换

如果要确保数据的完整性，应该使用csscan扫描数据库，找出所有不兼容的字符，然后通过编写相应的脚本及代码，在转换之后进行更新，确保数据的正确性。

简单看一下csscan的使用。要使用csscan之前，需要以sys用户身份创建相应数据字典对象：

E:\nls2>sqlplus　"/ as sysdba"

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production

With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options

JServer Release 9.2.0.4.0 - Production

SQL> select instance_name from v$instance;

INSTANCE_NAME

----------------

eygle

SQL> @?/rdbms/admin/csminst.sql

User created.

Grant succeeded.

……

这个脚本创建相应用户（csmig）及数据字典对象，扫描信息会记录在相应的数据字典表里。可以在命令行调用这个工具对数据库进行扫描：

E:\nls2>csscan FULL=Y FROMCHAR=ZHS16GBK TOCHAR=US7ASCII LOG=US7check.log CAPTURE=Y ARRAY=1000000 PROCESS=2

Character Set Scanner v1.1　: Release 9.2.0.1.0 - Production on Sun Nov 2 20:24:45 2003

Copyright (c) 1982, 2002, Oracle Corporation.　All rights reserved.

Username: eygle/eygle

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production

With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options

JServer Release 9.2.0.4.0 - Production

Enumerating tables to scan...

. process 1 scanning SYS.SOURCE$[AAAABHAABAAAAIRAAA]

. process 2 scanning SYS.ATTRIBUTE$[AAAAEoAABAAAAhZAAA]

. process 2 scanning SYS.PARAMETER$[AAAAEoAABAAAAhZAAA]

. process 2 scanning SYS.METHOD$[AAAAEoAABAAAAhZAAA]

……

. process 2 scanning SYSTEM.DEF$_AQERROR[AAAA8fAABAAACWJAAA]

. process 1 scanning WMSYS.WM$ENV_VARS[AAABeWAABAAAFMZAAA]

……

. process 2 scanning SYS.UGROUP$[AAAAA5AABAAAAGpAAA]

. process 2 scanning SYS.CON$[AAAAAcAABAAAACpAAA]

. process 1 scanning SYS.FILE$[AAAAARAABAAAABxAAA]

Creating Database Scan Summary Report...

Creating Individual Exception Report...

Scanner terminated successfully.

然后可以检查输出的日志来查看数据库扫描情况：

Database Scan Individual Exception Report

[Database Scan Parameters]

Parameter　　　　　　　Value

------------------------------ ------------------------------------------------

Scan type　　　　　　Full database

Scan CHAR data?　　　　　YES

Current database character set ZHS16GBK

New database character set　US7ASCII

Scan NCHAR data?　　　　NO

Array fetch buffer size　　1000000

Number of processes　　　2

Capture convertible data?　　YES

------------------------------ ------------------------------------------------

[Data Dictionary individual exceptions]

[Application data individual exceptions]

User　: EYGLE

Table　: TEST

Column: NAME

Type　: VARCHAR2(10)

Number of Exceptions　　　　: 1

Max Post Conversion Data Size: 4

ROWID　　　　Exception Type　　Size Cell Data(first 30 bytes)

------------------ ------------------ ----- ------------------------------

AAABpIAADAAAAAMAAA lossy conversion　测试

------------------ ------------------ ----- ------------------------------

不能转换的数据将会被记录下来，然后可以根据这些信息在转换之后，对数据进行相应的更新，确保转换无误。