上篇中，筆者演示瞭如何安裝呼叫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

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

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工具是一種很方便的字符集轉換檢驗工具。透過簡單的分析處理，可以避免很多在遷移轉換過程中的資料缺失風險。一些可以規避的錯誤和問題，可以在遷移規劃階段就進行處理。

使用Ccscan進行資料字符集轉換驗證（下）

相關文章