Oracle 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
[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

參考：