csscan安装使用指南
1.概述
csscan工具全程大名：Character Set Scanner（字符集扫描工具）
该工具可以用来在进行字符集转换前检查所有的表是否能够进行成功转换
安装请参考如下文档：
9i：请参考：
Installing and Configuring Csscanin 8i and 9i (Database Character Set Scanner) [ID 458122.1]
10g and up请参考：
Installing and configuring Csscanin 10g and 11g (Database Character Set Scanner) [ID 745809.1]
csscan输出解释请参考：
Csscan output explained [ID444701.1]
首先，查看一下ccscan的帮助：
[oracle@oel48ccscan]$ csscan HELP=y
CharacterSet Scanner v1.1 : Release 9.2.0.1.0 - Production on Wed Jul 13 11:40:30 2011
Copyright(c) 1982, 2002, Oracle Corporation. Allrights reserved.
You canlet Scanner prompt you for parameters by entering the CSSCAN
commandfollowed by your username/password:
Example: CSSCAN SYSTEM/MANAGER
Or, youcan control how Scanner runs by entering the CSSCAN command
followedby various parameters. To specify parameters, you use keywords:
Example: CSSCAN SYSTEM/MANAGER FULL=yTOCHAR=utf8 ARRAY=102400 PROCESS=3
Keyword Default Prompt Description
----------------- ------ -------------------------------------------------
USERID yes username/password
FULL N yes scan entire database
USER yes user name of the table to scan
TABLE yes list of tables to scan
EXCLUDE list of tables to excludefrom scan
TOCHAR yes new database character set name
FROMCHAR current database characterset name
TONCHAR new NCHAR character setname
FROMNCHAR current NCHAR character setname
ARRAY 10240 yes size of array fetchbuffer
PROCESS 1 yes number of scan process
MAXBLOCKS split table if larger thanMAXBLOCKS
CAPTURE N capture convertible data
SUPPRESS suppress error log by N pertable
FEEDBACK feedback progress every Nrows
BOUNDARIES list of column size boundariesfor summary report
LASTRPT N generate report of the lastdatabase scan
LOG scan base name of log files
PARFILE parameter file name
PRESERVE N preserve existing scanresults
HELP N show help screen (thisscreen)
----------------- ------ -------------------------------------------------
Scannerterminated successfully.
2.安装
在使用csscan之前，需要执行以sys用户登陆，创建所需的CSMIG用户和数据字典对象：
cd $ORACLE_HOME/rdbms/admin
set oracle_sid=<your SID>
sqlplus /nolog
SQL>conn / as sysdba
SQL>set TERMOUT ON
SQL>set ECHO ON
SQL>spool csminst.log
SQL>@?/rdbms/admin/csminst.sql
The password for theCSMIG user will be asked when running the csminst.sql script, this can beanything or the account can be locked afterwards, the actual csmig login is notused by Csscan.
Check the csminst.log for errors.
执行方法：
$ csscan userid="'"sys/sys assysdba"'" full=y tochar=ZHS16GBK log=check.log capture=yarray=1000000 process=1
csscan会生成3种输出文件，文件以LOG=参数为前缀，如这里为check.log，生成的3个输出文件为：
charcheck.out -Scan log -- provides a complete list of all scanned items
charcheck.txt - Database Scan Summary -- Summary of scan results
charcheck.err - Individual Exceptions -- Excerpted view of scan exceptions.
.err文件的生成取决于CAPTURE=Y或CAPTURE=N的参数
3.Conversion Summary的状态说明：
[Applicationdata individual exceptions]
User : SCOTT
Table: T2
Column:NAME
Type : VARCHAR2(100)
Numberof Exceptions : 1
MaxPost Conversion Data Size: 9
ROWID Exception Type Size Cell Data(first 30 bytes)
------------------------------------ ----- ------------------------------
AAABlCAAFAAADDkAAAlossy conversion 农家乐
------------------------------------ ----- ------------------------------
CHANGELESS 表明转换前后，字符编码不改变
CONVERTIBLE 表明这些数据可以转换，但是在使用Csalter或“Alter database character set”命令之前，这些数据需要export出来，然后truncate或删除，并在转换完字符集后，再导入。
TRUNCATION 这是一种CONVERTIBLE的特例，字段长度不够，在转换后，内容会被截断。
LOSSY 这些数据转换后会丢失
4.修改字符集
SHUTDOWN IMMEDIATE;
-- make sure there is adatabase backup you can rely on, or create one
STARTUP MOUNT;
ALTER SYSTEM ENABLE RESTRICTEDSESSION;
ALTER SYSTEM SETJOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SETAQ_TM_PROCESSES=0;
ALTER DATABASE OPEN;
ALTER DATABASE CHARACTER SET<new_character_set>;
-- a alter database takestypically only a few minutes or less,
-- it depends on the number ofcolumns in the database, not the
-- amount of data.
SHUTDOWN;
-- If you use Oracle8 then alsodo:
STARTUP RESTRICT;
SHUTDOWN;
修改字符集的方法，请参考：
8i/9i only: Changing theDatabase Character Set or the Database National Character Set in 8i/9i [ID66320.1]
这边，数据库从US7ASCII修改为ZHS16GBK后，因为有中文，所以报错
SQL> column name format a15
SQL> column dump format a30
SQL> column dump format a60
SQL> select name, dump(name) as dumpfrom t2;
SP2-0784: Invalid or incomplete characterbeginning 0x90 returned
NAME DUMP
---------------------------------------------------------------------------
Pei Typ=1 Len=3: 80,101,105
SQL> select * from t2;
SP2-0784: Invalid or incomplete characterbeginning 0x90 returned
ID NAME
---------- ---------------
2 Pei