您好,登錄后才能下訂單哦!
轉(zhuǎn)載自:http://blog.itpub.net/4227/viewspace-706635/
工具csscan用于檢查從一個(gè)字符集轉(zhuǎn)換到另一個(gè)字符集,數(shù)據(jù)庫(kù)中的數(shù)據(jù)是否會(huì)產(chǎn)生丟失、截?cái)嗟痊F(xiàn)象。
這個(gè)工具很早就出現(xiàn)了,不過(guò)由于長(zhǎng)久以來(lái)牽制到字符集轉(zhuǎn)換的工作不是很多,因此對(duì)于這個(gè)工具沒什么研究,這次需要將ZHS16GBK轉(zhuǎn)換到AL32UTF8,嘗試了一下csscan的功能,發(fā)現(xiàn)這個(gè)工具還是很方便的。
[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.
錯(cuò)誤信息很明顯,連接用戶不是DBA角色,如果用system用戶連接進(jìn)行這個(gè)命令:
[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.
導(dǎo)致這個(gè)錯(cuò)誤是由于CSSCAN工具需要在數(shù)據(jù)庫(kù)中建立一個(gè)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.
調(diào)用csminst.sql腳本創(chuàng)建輔助用戶和對(duì)象后,再次運(yùn)行csscan工具,對(duì)表中數(shù)據(jù)進(jìn)行轉(zhuǎn)換前的掃描。
工具csscan的調(diào)用有命令行方式,和交互兩種,當(dāng)命令行沒有提供足夠的參數(shù),工具會(huì)以交互方式獲取其他參數(shù)。
操作完成后,可以檢查對(duì)應(yīng)的日志信息:
[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會(huì)生成三個(gè)日志,一個(gè)err記錄錯(cuò)誤信息,比如這個(gè)例子中,一個(gè)列的長(zhǎng)度需要變長(zhǎng),否則無(wú)法容納數(shù)據(jù)長(zhǎng)度的擴(kuò)展;一個(gè)log記錄操作步驟;而txt則是最終的匯總信息。
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。