csscan使用

数据迁移的时候可以使用csscan工具扫描数据库，把一些不兼容的字符查出来(例如：ZHS16GBK中一个汉字占两个字节，在AL32UTF8中占用3个字节，这就可能导致数据从ZHS16GBK到AL32UTF8时，字符溢出，数据无法导入)。

CSSCAN使用步骤：

1） @?/rdbms/admin/csminst.sql

……

Synonym created.

View created.

View created.

Commit complete.

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

2）csscan schemas=ggx fromchar=ZHS16GBK tochar=AL32UTF8 log=/home/oracle/expdp/abc.log

其中 fromchar是源字符集，tochar是目标字符集，log是生成日子的路径。

3） [oracle@ggx expdp]$ ls -l abc*
-rw-r--r-- 1 oracle oinstall 1800 03-14 22:10 abc.log.err
-rw-r--r-- 1 oracle oinstall 592 03-14 22:10 abc.log.out
-rw-r--r-- 1 oracle oinstall 5690 03-14 22:10 abc.log.txt

共生成3个日子文件，其中.err是记录数据不兼容错误信息的：

[oracle@ggx expdp]$ cat *.err
Database Scan Individual Exception Report

[Database Scan Parameters]

Parameter Value
------------------------------ ------------------------------------------------
CSSCAN Version v2.1
Instance Name orcl
Database Version 10.2.0.1.0
Scan type Selective tables
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 : GGX
Table : ABC
Column: NAME
Type : VARCHAR2(4)
Number of Exceptions : 1
Max Post Conversion Data Size: 6

ROWID Exception Type Size Cell Data(first 30 bytes)
------------------ ------------------ ----- ------------------------------
AAAM4yAAFAAAAAUAAA exceed column size 6 中国
------------------ ------------------ ----- ------------------------------

.out是记录执行过程的：

[oracle@ggx expdp]$ cat *out

Character Set Scanner v2.1 : Release 10.2.0.0.0 - Production on Wed Mar 14 22:09:32 2012

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Username:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Enter array fetch buffer size: 1024000 >
Enter number of scan processes to utilize(1..32): 1 >
Enumerating table to scan...

. process 1 scanning GGX.ABC[AAAM4yAAFAAAAARAAA]

Creating Database Scan Summary Report...

Creating Individual Exception Report...

Scanner terminated successfully.

.txt是记录最终汇总信息的：

[oracle@ggx expdp]$ cat *.txt
Database Scan Summary Report

Time Started : 2012-03-14 22:09:48
Time Completed: 2012-03-14 22:09:59

Process ID Time Started Time Completed
---------- -------------------- --------------------
1 2012-03-14 22:09:58 2012-03-14 22:09:58
---------- -------------------- --------------------

[Database Size]

Tablespace Used Free Total Expansion
------------------------- --------------- --------------- --------------- ---------------
SYSTEM 476.13M 3.88M 480.00M .00K
UNDOTBS1 19.19M 65.81M 85.00M .00K
SYSAUX 247.25M 2.75M 250.00M .00K
TEMP .00K .00K .00K .00K
USERS 448.00K 4.56M 5.00M .00K
GGX 128.00K 9.88M 10.00M .00K
------------------------- --------------- --------------- --------------- ---------------
Total 743.13M 86.88M 830.00M .00K

[Database Scan Parameters]

Parameter Value
------------------------------ ------------------------------------------------
CSSCAN Version v2.1
Instance Name orcl
Database Version 10.2.0.1.0
Scan type Selective tables
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]

Datatype Changeless Convertible Truncation Lossy
--------------------- ---------------- ---------------- ---------------- ----------------
VARCHAR2 0 0 0 0
CHAR 0 0 0 0
LONG 0 0 0 0
CLOB 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 1 1 0
CHAR 0 0 0 0
LONG 0 0 0 0
CLOB 0 0 0 0
VARRAY 0 0 0 0
--------------------- ---------------- ---------------- ---------------- ----------------
Total 0 1 1 0
Total in percentage 0.000% 50.000% 50.000% 0.000%

[Distribution of Convertible, Truncated and Lossy Data by Table]

USER.TABLE Convertible Truncation Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
GGX.ABC 1 1 0
-------------------------------------------------- ---------------- ---------------- ----------------

[Distribution of Convertible, Truncated and Lossy Data by Column]

USER.TABLE|COLUMN Convertible Truncation Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
GGX.ABC|NAME 1 1 0
-------------------------------------------------- ---------------- ---------------- ----------------

[Indexes to be Rebuilt]

USER.INDEX on USER.TABLE(COLUMN)
-----------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25517773/viewspace-730318/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25517773/viewspace-730318/