CSSCAN扫描字符集转换耗损

不同字符集间的转换，可以使用csscan先扫描一下看看是否可以成功




Installing the Database Character Set Scanner System Tables


CSSCAN使用条件：
1.sys用户执行csminst.sql
2.执行csscan用户需要有sysdba权限


csminst.sql脚本包含功能：
Creates a user named CSMIG
Assigns the necessary privileges to CSMIG
Assigns the default tablespace to CSMIG
Creates the Character Set Scanner system tables under CSMIG


--用户CSMIG默认表空间为system，你可以修改这个CSMIG的默认表空间，去修改csminst.sql






1.sys用户执行csminst.sql


SQL> conn /as sysdba
Connected.
SQL> @?/rdbms/admin/csminst.sql


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.


drop public synonym csm$parameters
                    *
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist






Synonym created.




Table created.


drop public synonym csm$query
                    *
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist






Synonym created.




Table created.


drop public synonym csm$tables
                    *
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist






Synonym created.




Table created.


drop public synonym csm$columns
                    *
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist






Synonym created.




Table created.


drop public synonym csm$extables
                    *
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist






Synonym created.




Table created.


drop public synonym csm$errors
                    *
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist






Synonym created.




Table created.


drop public synonym csm$langid
                    *
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist






Synonym created.




Table created.


drop public synonym csm$charsetid
                    *
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist






Synonym created.




Table created.


drop public synonym csm$indexes
                    *
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist






Synonym created.




Table created.


drop public synonym csm$constraints
                    *
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist






Synonym created.




Table created.


drop public synonym csm$triggers
                    *
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist






Synonym created.




Table created.


drop public synonym csm$dictusers
                    *
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist






Synonym created.




17 rows created.




View created.


drop public synonym csmv$tables
                    *
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist






Synonym created.




View created.


drop public synonym csmv$columns
                    *
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist






Synonym created.




View created.


drop public synonym csmv$errors
                    *
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist






Synonym created.




View created.


drop public synonym csmv$indexes
                    *
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist






Synonym created.




View created.


drop public synonym csmv$constraints
                    *
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist






Synonym created.




View created.


drop public synonym csmv$triggers
                    *
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist






Synonym created.




View created.




View created.




View created.




View created.




Grant succeeded.




Grant succeeded.


Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


2.csscan全库扫描


[oracle@DOU dba]$  csscan " 'sys as sysdba' " full=y  fromchar=ZHS16GBK tochar=AL32UTF8 log=verify.log array=102400 process=6




Character Set Scanner v2.2 : Release 11.2.0.1.0 - Production on Wed Apr 16 09:30:53 2014


Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.




Password: 


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


Enumerating tables to scan...


. process 1 scanning SF_CF_PREPROD.CONTRACT_HTR[AAKdzCAAKAAAqQAAAA]
. process 2 scanning SF_CF_PREPROD.CONTRACT_HTR[AAKdzCAAJAAA34AAAA]
. process 3 scanning SYS.AUD$[AAAAF/AABAAAAj4AAA]
. process 6 scanning SF_CF_UAT2.CONTRACT_HTR[AAKERWAAKAAAXgAAAA]
. process 4 scanning SF_CF_UAT2.CONTRACT_HTR[AAKERWAAJAABjeAAAA]
. process 5 scanning SF_CF_UAT.CONTRACT_HTR[AAINwiAAJAAAM0AAAA]
. process 5 scanning SYS.WRH$_ACTIVE_SESSION_HISTORY[AACFEWAACAAAA+YAAA]
.....................


Creating Database Scan Summary Report...


Creating Individual Exception Report...


[oracle@DOU dba]$ ls -l
total 99820
-rw-r--r--. 1 oracle oinstall 100932427 Apr 16 09:46 verify.log.err
-rw-r--r--. 1 oracle oinstall    741306 Apr 16 09:46 verify.log.out
-rw-r--r--. 1 oracle oinstall    355242 Apr 16 09:39 verify.log.txt


[oracle@DOU dba]$ more verify.log.txt


Database Scan Summary Report


Time Started  : 2014-04-16 09:30:58
Time Completed: 2014-04-16 09:39:50


Process ID         Time Started       Time Completed
---------- -------------------- --------------------
         1  2014-04-16 09:31:32  2014-04-16 09:39:47
         2  2014-04-16 09:31:32  2014-04-16 09:39:47
         3  2014-04-16 09:31:32  2014-04-16 09:39:47
         4  2014-04-16 09:31:32  2014-04-16 09:39:47
         5  2014-04-16 09:31:32  2014-04-16 09:39:47
         6  2014-04-16 09:31:32  2014-04-16 09:39:47
---------- -------------------- --------------------


[Database Size]


Tablespace                           Used            Free           Total       Expansi
on
------------------------- --------------- --------------- --------------- -------------
--
SYSTEM                          3,547.81M           5.19M       3,553.00M         107.00K
SYSAUX                          9,653.75M         486.25M      10,140.00M           4.24M
USERS                           2,562.81M         152.19M       2,715.00M          28.86M
UNDOTBS2                           61.81M       1,862.19M       1,924.00M            .00K
IMAGING                           537.31M       2,842.69M       3,380.00M            .00K


[Database Scan Parameters]


Parameter                      Value                                           
------------------------------ ------------------------------------------------
CSSCAN Version                 v2.1                                            
Instance Name                  DOU                                             
Database Version               11.2.0.1.0                                      
Scan type                      Full database                                   
Scan CHAR data?                YES                                             
Database character set         ZHS16GBK                                        
FROMCHAR                       ZHS16GBK                                        
TOCHAR                         AL32UTF8                                        
Scan NCHAR data?               NO                                              
Array fetch buffer size        102400                                          
Number of processes            6                                               
Capture convertible data?      NO                                              
------------------------------ ------------------------------------------------


[Scan Summary]


Some character type data in the data dictionary are not convertible to the new character set
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                   355,318,245          490,843               79            1,502
CHAR                           459,243                0                0                0
LONG                           530,377              111                0                0
VARRAY                          42,148                0                0                0
--------------------- ---------------- ---------------- ---------------- ----------------
Total                      356,350,013          490,954               79            1,502
Total in percentage             99.862%           0.138%           0.000%           0.000%


The data dictionary can not be safely migrated using the CSALTER script


XML CSX Dictionary Tables:


Datatype                    Changeless      Convertible       Truncation            Lossy
--------------------- ---------------- ---------------- ---------------- ----------------
VARCHAR2                           495                0                0                0
CHAR                                 0                0                0                0
LONG                                 0                0                0                0
VARRAY                               0                0                0                0
--------------------- ---------------- ---------------- ---------------- ----------------
Total                              495                0                0                0
Total in percentage            100.000%           0.000%           0.000%           0.000%


应用类型数据注意的重点
[Application Data Conversion Summary]


Datatype                    Changeless      Convertible       Truncation            Lossy
--------------------- ---------------- ---------------- ---------------- ----------------
VARCHAR2                 3,057,532,926       30,482,331          852,104                0
CHAR                        56,973,885            3,811                0                0
LONG                                 0                0                0                0
VARRAY                           6,912                0                0                0
--------------------- ---------------- ---------------- ---------------- ----------------
Total                    3,114,513,723       30,486,142          852,104                0
Total in percentage             99.004%           0.969%           0.027%           0.000%


详细说明表转换后的状况
[Distribution of Convertible, Truncated and Lossy Data by Table]


Data Dictionary Tables:


USER.TABLE                                              Convertible       Truncation            Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
SYS.AUD$                                                         20                0                0
SYS.CDEF$                                                         3                0                0
SYS.COL$                                                        133                3                0
SYS.COM$                                                      1,086                0                0
SYS.ERROR$                                                    1,016                0                0
SYS.HISTGRM$                                                  5,485                0                0
SYS.IDL_CHAR$                                                     3                0                0
SYS.SCHEDULER$_JOB                                                4                0                0
SYS.SOURCE$                                                   6,565                0                0
SYS.TRIGGER$                                                      3                0                0
SYS.VIEW$                                                       102                0                0
SYS.WRH$_ACTIVE_SESSION_HISTORY                                 100               76                0

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

转载于:http://blog.itpub.net/26442936/viewspace-1426242/