oracle的字元集檢查工具CSSCAN(三)

oracle 提供了兩個關于字元集檢查的工具，一個csscan,一個是lcsscan（language and character set file scanner）

一 database character set scanner(csscan)

剛開始使用csscan時會遇到 css-00107錯誤，

css-00107: character set migration utility schema not installed

原因可以從提示得到：未安裝csscan.

是以先安裝csscan

以sys 身份執行如下語句：

sys@oracl> @d:\oracle\product\11.1.0\db_1\rdbms\admin\csminst.sql

授權成功。

使用者已删除。

please create password for user csmig:

輸入 csmig_passwd 的值:  yang

原值    1: create user csmig identified by &csmig_passwd

新值    1: create user csmig identified by yang

使用者已建立。

........

使用者已更改。

已建立0行。

已更新 1 行。

同義詞已建立。

......

視圖已建立。

送出完成。

2）使用 csscan工具對資料庫進行檢查。被檢測資料庫是zhs16gbk,要導入到al32utf8中的資料庫中，檢查是否有在資料遷移過程中是否有錯誤。

c:\users\aaaa>csscan \"system/yang as sysdba \" user=yang tochar=al32utf8 array=104000 process=2

character set scanner v2.2 : release 11.1.0.6.0 - production on 星期一 3月 14 12:19:06 2011

copyright (c) 1982, 2007, oracle.  all rights reserved.

connected to:

oracle database 11g enterprise edition release 11.1.0.6.0 - production

with the partitioning, olap, data mining and real application testing options

enumerating tables to scan...

. process 1 scanning yang.test[aaarwyaagaaaaajaaa]

. process 2 scanning yang.test[aaarwyaagaaah+jaaa]

. process 1 scanning yang.test[aaarwyaaeaaaagjaaa]

. process 2 scanning yang.test_char[aaarjpaagaaaabhaaa]

. process 1 scanning yang.fixed[aaarfqaagaaaabraaa]

. process 2 scanning yang.test_byte[aaarjoaagaaaabzaaa]

. process 2 scanning yang.a[aaarbbaaeaaaakbaaa]

. process 2 scanning yang.dept[aaarz8aaeaaadpjaaa]

. process 1 scanning yang.fixed_char[aaarfgaagaaaabbaaa]

. process 2 scanning yang.tchar[aaarbhaaeaaadojaaa]

creating database scan summary report...

creating individual exception report...

scanner terminated successfully.

檢視生成的日志檔案：如果不指定具體的log 檔案，預設會在執行指令的目前目錄下産生scan.out  scan.txt scan.err,這裡我們比較關注scan.err 因為它裡面包含

字元集轉換出錯的資料。

database scan individual exception report

[database scan parameters]

parameter                      value                                           

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

csscan version                 v2.1                                            

instance name                  oracl                                           

database version               11.1.0.6.0                                      

scan type                      user tables                                     

user name                      yang                                            

scan char data?                yes                                             

database character set         zhs16gbk                                        

fromchar                       zhs16gbk                                        

tochar                         al32utf8                                        

scan nchar data?               no                                              

array fetch buffer size        104000                                          

number of processes            2                                               

capture convertible data?      no                                              

[data dictionary individual exceptions]

[application data individual exceptions]

user  : yang

table : fixed  ---轉換出錯的表

column: val2   ---轉換出錯的字段。

type  : char(15)

number of exceptions         : 499    --行數。   

max post conversion data size: 21        

rowid              exception type      size cell data(first 30 bytes)     

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

aaarfqaagaaaabvaa+ exceed column size   21 阿裡雲計算公司                

aaarfqaagaaaabvaa/ exceed column size    21 阿裡雲計算公司                

aaarfqaagaaaabvaa0 exceed column size    21 阿裡雲計算公司                

aaarfqaagaaaabvaa1 exceed column size    21 阿裡雲計算公司                

aaarfqaagaaaabvaa2 exceed column size    21 阿裡雲計算公司                

aaarfqaagaaaabvaa3 exceed column size    21 阿裡雲計算公司                

aaarfqaagaaaabvaa4 exceed column size    21 阿裡雲計算公司                

aaarfqaagaaaabvaa5 exceed column size    21 阿裡雲計算公司                  

               。。。。。。。。                 

aaarfqaagaaaabvabj exceed column size    21 阿裡雲計算公司                

aaarfqaagaaaabvabk exceed column size    21 阿裡雲計算公司                

aaarfqaagaaaabvabl exceed column size    21 阿裡雲計算公司                

aaarfqaagaaaabvabm exceed column size   21 阿裡雲計算公司                

aaarfqaagaaaabvabn exceed column size    21 阿裡雲計算公司                

aaarfqaagaaaabvabo exceed column size    21 阿裡雲計算公司                

aaarfqaagaaaabvabp exceed column size    21 阿裡雲計算公司