您好，登录后才能下订单哦！
密码登录
登录注册
点击 登录注册 即表示同意《亿速云用户服务条款》
转载自：http://blog.itpub.net/4227/viewspace-706635/
工具csscan用于检查从一个字符集转换到另一个字符集，数据库中的数据是否会产生丢失、截断等现象。
这个工具很早就出现了，不过由于长久以来牵制到字符集转换的工作不是很多，因此对于这个工具没什么研究，这次需要将ZHS16GBK转换到AL32UTF8，尝试了一下csscan的功能，发现这个工具还是很方便的。
[oracle@dbserver1 bin]$ csscan userid=thams/thams table=libfile722 tochar=AL32UTF8 log=/home/oracle/scan_722
Character Set Scanner v2.2 : Release 11.2.0.2.0 - Production on Wed Aug 31 10:54:15 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates.
CSS-00127: user thams does not have DBA privilege
Scanner terminated unsuccessfully.
错误信息很明显，连接用户不是DBA角色，如果用system用户连接进行这个命令：
[oracle@dbserver1 bin]$ csscan userid=system/oracle table=thams.libfile722 tochar=AL32UTF8 log=/home/oracle/scan_722
Character Set Scanner v2.2 : Release 11.2.0.2.0 - Production on Wed Aug 31 10:54:45 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
CSS-00107: Character set migration utility schema not installed
Scanner terminated unsuccessfully.
导致这个错误是由于CSSCAN工具需要在数据库中建立一个CSMIG用户：
[oracle@dbserver1 bin]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Wed Aug 31 10:57:28 2011
Copyright (c) 1982, 2010, Oracle.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> @?/rdbms/admin/csminst
User created.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
User altered.
1 row created.
1 row updated.
Table created.
.
.
.
View created.
View created.
View created.
View created.
Grant succeeded.
Grant succeeded.
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
[oracle@dbserver1 bin]$ csscan userid=system/oracle table=thams.libfile722 tochar=AL32UTF8 log=/home/oracle/scan_722
Character Set Scanner v2.2 : Release 11.2.0.2.0 - Production on Wed Aug 31 10:59:35 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Enter array fetch buffer size: 1024000 > 4096000
Enter number of scan processes to utilize(1..64): 1 >
Enumerating table to scan...
. process 1 scanning THAMS.LIBFILE722[AAAP2KAAAAAB7XYAAA]
Creating Database Scan Summary Report...
Creating Individual Exception Report...
Scanner terminated successfully.
调用csminst.sql脚本创建辅助用户和对象后，再次运行csscan工具，对表中数据进行转换前的扫描。
工具csscan的调用有命令行方式，和交互两种，当命令行没有提供足够的参数，工具会以交互方式获取其他参数。
操作完成后，可以检查对应的日志信息：
[oracle@dbserver1 bin]$ more /home/oracle/scan_722.err
Database Scan Individual Exception Report
[Database Scan Parameters]
Parameter
------------------------------ ------------------------------------------------
CSSCAN Version
Instance Name
Database Version
Scan type
Scan CHAR data?
Database character set
FROMCHAR
TOCHAR
Scan NCHAR data?
Array fetch buffer size
Number of processes
Capture convertible data?
------------------------------ ------------------------------------------------
[Data Dictionary individual exceptions]
[Application data individual exceptions]
User
Table : LIBFILE722
Column: F4
Type
Number of Exceptions
Max Post Conversion Data Size: 45
ROWID
------------------ ------------------ ----- ------------------------------
AAAP2KAAAAAB+u0AAE exceed column size
------------------ ------------------ ----- ------------------------------
[oracle@dbserver1 bin]$ more /home/oracle/scan_722.out
Character Set Scanner v2.2 : Release 11.2.0.2.0 - Production on Wed Aug 31 10:59:35 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Enter array fetch buffer size: 1024000 >
Enter number of scan processes to utilize(1..64): 1 >
Enumerating table to scan...
. process 1 scanning THAMS.LIBFILE722[AAAP2KAAAAAB7XYAAA]
Creating Database Scan Summary Report...
Creating Individual Exception Report...
Scanner terminated successfully.
[oracle@dbserver1 bin]$ more /home/oracle/scan_722.txt
Database Scan Summary Report
Time Started
Time Completed: 2011-08-31 10:59:50
Process ID
---------- -------------------- --------------------
---------- -------------------- --------------------
[Database Size]
Tablespace
------------------------- --------------- --------------- --------------- ---------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
LOB_AU2M
LOB_AU8M
DATA_ARCHIVE
LOB_AU32M
LOB_AU64M
------------------------- --------------- --------------- --------------- ---------------
Total
[Database Scan Parameters]
Parameter
------------------------------ ------------------------------------------------
CSSCAN Version
Instance Name
Database Version
Scan type
Scan CHAR data?
Database character set
FROMCHAR
TOCHAR
Scan NCHAR data?
Array fetch buffer size
Number of processes
Capture convertible data?
------------------------------ ------------------------------------------------
[Scan Summary]
Some character type application data are not convertible to the new character set
[Data Dictionary Conversion Summary]
Data Dictionary Tables:
Datatype
--------------------- ---------------- ---------------- ---------------- ----------------
VARCHAR2
CHAR
LONG
VARRAY
--------------------- ---------------- ---------------- ---------------- ----------------
Total
Total in percentage
XML CSX Dictionary Tables:
Datatype
--------------------- ---------------- ---------------- ---------------- ----------------
VARCHAR2
CHAR
LONG
VARRAY
--------------------- ---------------- ---------------- ---------------- ----------------
Total
Total in percentage
[Application Data Conversion Summary]
Datatype
--------------------- ---------------- ---------------- ---------------- ----------------
VARCHAR2
CHAR
LONG
VARRAY
--------------------- ---------------- ---------------- ---------------- ----------------
Total
Total in percentage
[Distribution of Convertible, Truncated and Lossy Data by Table]
Data Dictionary Tables:
USER.TABLE
---------------------------------------- ---------------- ---------------- ----------------
---------------------------------------- ---------------- ---------------- ----------------
XML CSX Dictionary Tables:
USER.TABLE
---------------------------------------- ---------------- ---------------- ----------------
---------------------------------------- ---------------- ---------------- ----------------
Application Data:
USER.TABLE
---------------------------------------- ---------------- ---------------- ----------------
THAMS.LIBFILE722
---------------------------------------- ---------------- ---------------- ----------------
[Distribution of Convertible, Truncated and Lossy Data by Column]
Data Dictionary Tables:
USER.TABLE|COLUMN
---------------------------------------- ---------------- ---------------- ----------------
---------------------------------------- ---------------- ---------------- ----------------
XML CSX Dictionary Tables:
USER.TABLE|COLUMN
---------------------------------------- ---------------- ---------------- ----------------
---------------------------------------- ---------------- ---------------- ----------------
Application Data:
USER.TABLE|COLUMN
---------------------------------------- ---------------- ---------------- ----------------
THAMS.LIBFILE722|F11
THAMS.LIBFILE722|F2
THAMS.LIBFILE722|F3
THAMS.LIBFILE722|F46
THAMS.LIBFILE722|F6
THAMS.LIBFILE722|F7
THAMS.LIBFILE722|TITLE
---------------------------------------- ---------------- ---------------- ---------------
[Indexes to be Rebuilt]
USER.INDEX on USER.TABLE(COLUMN)
-----------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------
工具csscan会生成三个日志，一个err记录错误信息，比如这个例子中，一个列的长度需要变长，否则无法容纳数据长度的扩展；一个log记录操作步骤；而txt则是最终的汇总信息。
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。