上篇中,笔者演示了如何安装调用Ccscan工具,下面继续通过演示实验进行测试。
4、演示实验
下面笔者进行一个简单实验,通过schema范围的数据表扫描,来进行Ccscan工作分析。
SQL> select * from nls_database_parameters where parameter='NLS_CHARACTERSET';
PARAMETER VALUE
------------------------------ ----------------------------------
NLS_CHARACTERSET ZHS16GBK
SQL> create user test identified by test;
User created
SQL> grant connect, resource to test;
Grant succeeded
SQL> create table test.t (name varchar2(4));
Table created
当前实验数据表T所在数据库字符集ZHS16GBK,name字段长度为varchar2(4)。下面向其中插入中文字符。
SQL> insert into test.t values ('测试');
SQL> insert into test.t values ('测试中');
insert into test.t values ('测试中')
ORA-12899: 列 "TEST"."T"."NAME" 的值太大 (实际值: 6, 最大值: 4)
SQL> desc test.t
Name Type Nullable Default Comments
---- ----------- -------- ------- --------
NAME VARCHAR2(4) Y
SQL> select * from test.t;
NAME
----
测试
在当前字符集编码方式下,name列能容纳两个中文字已经是极限了。当尝试插入三个中文字的时候,系统报错。
下面进行扫描处理。
[oracle@ddd ~]$ csscan user=test fromchar=zhs16gbk tochar=al32utf8 log=testchange capture=n process=1
Character Set Scanner v2.2 : Release 11.2.0.4.0 - Production on Wed Sep 2 10:52:42 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Username: system
Password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Enter array fetch buffer size: 1024000 >
Enumerating tables to scan...
. process 1 scanning TEST.T[AAAWB0AAEAAAACoAAA]
Creating Database Scan Summary Report...
Creating Individual Exception Report...
Scanner terminated successfully.
结果文件正确生成。
[oracle@ddd ~]$ ls -l | grep test
-rw-r--r--. 1 oracle oinstall 1879 Sep 2 10:52 testchange.err
-rw-r--r--. 1 oracle oinstall 590 Sep 2 10:52 testchange.out
-rw-r--r--. 1 oracle oinstall 8136 Sep 2 10:52 testchange.txt
下面分别查看结果文件内容。
5、结果文件分析
首先我们查看err文件,其中记录着异常信息。
[oracle@ddd ~]$ cat testchange.err
Database Scan Individual Exception Report
[Database Scan Parameters] –SCAN信息内容参数
Parameter Value
------------------------------ ------------------------------------------------
CSSCAN Version v2.1
Instance Name mmdb
Database Version 11.2.0.4.0
Scan type User tables
User name test
Scan CHAR data? YES
Database character set ZHS16GBK
FROMCHAR zhs16gbk
TOCHAR al32utf8
Scan NCHAR data? NO
Array fetch buffer size 1024000
Number of processes 1
Capture convertible data? NO
------------------------------ ------------------------------------------------
[Data Dictionary individual exceptions]
[Application data individual exceptions]
User : TEST
Table : T
Column: NAME
Type : VARCHAR2(4)
Number of Exceptions : 1
Max Post Conversion Data Size: 6
ROWID Exception Type Size Cell Data(first 30 bytes)
------------------ ------------------ ----- ------------------------------
AAAWB0AAEAAAACuAAA exceed column size 6 测试
------------------ ------------------ ----- ------------------------------
可以看到问题了,数据表test.t在转化字符集合过程中会有异常出现。异常数据的rowid信息、异常类型,期望修改的长度都可以清晰看到。
Out报告是执行程序过程中的日志记录,其中也有输出信息。
[oracle@ddd ~]$ cat testchange.out
Character Set Scanner v2.2 : Release 11.2.0.4.0 - Production on Wed Sep 2 10:52:42 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Username:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Enter array fetch buffer size: 1024000 >
Enumerating tables to scan...
. process 1 scanning TEST.T[AAAWB0AAEAAAACoAAA]
Creating Database Scan Summary Report...
Creating Individual Exception Report...
Scanner terminated successfully.
处理过程中,已经比较清楚的显示出问题数据表、数据行信息。
最后是结果汇总报告。
Database Scan Summary Report
Time Started : 2015-09-02 10:52:49
Time Completed: 2015-09-02 10:52:50
Process ID Time Started Time Completed
---------- -------------------- --------------------
1 2015-09-02 10:52:49 2015-09-02 10:52:49
---------- -------------------- --------------------
[Database Size]
Tablespace Used Free Total Expansion
------------------------- --------------- --------------- --------------- ---------------
SYSTEM 757.75M 12.25M 770.00M .00K
SYSAUX 574.38M 35.63M 610.00M .00K
UNDOTBS1 15.25M 189.75M 205.00M .00K
TEMP .00K .00K .00K .00K
USERS 1.38M 3.63M 5.00M .00K
------------------------- --------------- --------------- --------------- ---------------
Total 3,100.75M 413.25M 3,514.00M .00K
[Database Scan Parameters]
Parameter Value
------------------------------ ------------------------------------------------
CSSCAN Version v2.1
Instance Name mmdb
Database Version 11.2.0.4.0
Scan type User tables
User name test
Scan CHAR data? YES
Database character set ZHS16GBK
FROMCHAR zhs16gbk
TOCHAR al32utf8
Scan NCHAR data? NO
Array fetch buffer size 1024000
Number of processes 1
Capture convertible data? NO
------------------------------ ------------------------------------------------
[Scan Summary]
Some character type application data are not convertible to the new character set
[Data Dictionary Conversion Summary]
Data Dictionary Tables:
Datatype Changeless Convertible Truncation Lossy
--------------------- ---------------- ---------------- ---------------- ----------------
VARCHAR2 0 0 0 0
CHAR 0 0 0 0
LONG 0 0 0 0
VARRAY 0 0 0 0
--------------------- ---------------- ---------------- ---------------- ----------------
Total 0 0 0 0
Total in percentage 0.000% 0.000% 0.000% 0.000%
XML CSX Dictionary Tables:
Datatype Changeless Convertible Truncation Lossy
--------------------- ---------------- ---------------- ---------------- ----------------
VARCHAR2 0 0 0 0
CHAR 0 0 0 0
LONG 0 0 0 0
VARRAY 0 0 0 0
--------------------- ---------------- ---------------- ---------------- ----------------
Total 0 0 0 0
Total in percentage 0.000% 0.000% 0.000% 0.000%
[Application Data Conversion Summary]
Datatype Changeless Convertible Truncation Lossy
--------------------- ---------------- ---------------- ---------------- ----------------
VARCHAR2 0 0 1 0
CHAR 0 0 0 0
LONG 0 0 0 0
VARRAY 0 0 0 0
--------------------- ---------------- ---------------- ---------------- ----------------
Total 0 0 1 0
Total in percentage 0.000% 0.000% 100.000% 0.000%
[Distribution of Convertible, Truncated and Lossy Data by Table]
Data Dictionary Tables:
USER.TABLE Convertible Truncation Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
-------------------------------------------------- ---------------- ---------------- ----------------
XML CSX Dictionary Tables:
USER.TABLE Convertible Truncation Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
-------------------------------------------------- ---------------- ---------------- ----------------
Application Data:
USER.TABLE Convertible Truncation Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
TEST.T 0 1 0
-------------------------------------------------- ---------------- ---------------- ----------------
[Distribution of Convertible, Truncated and Lossy Data by Column]
Data Dictionary Tables:
USER.TABLE|COLUMN Convertible Truncation Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
-------------------------------------------------- ---------------- ---------------- ----------------
XML CSX Dictionary Tables:
USER.TABLE|COLUMN Convertible Truncation Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
-------------------------------------------------- ---------------- ---------------- ----------------
Application Data:
USER.TABLE|COLUMN Convertible Truncation Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
TEST.T|NAME 0 1 0
-------------------------------------------------- ---------------- ---------------- ----------------
[Indexes to be Rebuilt]
USER.INDEX on USER.TABLE(COLUMN)
-----------------------------------------------------------------------------------------
(略过……)
----------------------------------------------------------------------------------------
汇总报告内容比较多,也比较全面。包括了处理数据库空间、本次扫描处理过程的信息等内容。可以作为整体分析结果加以处理。最后部分的user.index部分,是建议的转换后进行索引重建的范围。
6、结论
Ccscan工具是一种很方便的字符集转换检验工具。通过简单的分析处理,可以避免很多在迁移转换过程中的数据缺失风险。一些可以规避的错误和问题,可以在迁移规划阶段就进行处理。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/17203031/viewspace-1791005/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/17203031/viewspace-1791005/