oracle修改字符集的小工具csscan

oracle修改字符集的小工具csscan

oracle提供了csscan工具用来修改字符集，csscan(Database Character Set Scanner)也可以单独的扫描某些表某些列能否进行字符集转换，并且能够并行扫描以加快扫描速度。

1.首先以sys用户身份创建用户(CSMIG)和相应的数据字典视图

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

User created.


Grant succeeded.


Grant succeeded.


Grant succeeded.


User altered.


1 row created.


1 row updated.

Connected.

......

Commit complete.

Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production
$ 

建立用户scott和demo表用来做测试，数据量小点，速度快些。

SQL> conn /as sysdba
Connected.
SQL> grant connect,resource to scott identified by tiger;

Grant succeeded.

SQL> conn scott/tiger
Connected.
SQL> @?/sqlplus/demo/demobld.sql
Building demonstration tables. Please wait.
Demonstration table build is complete.
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production
$ 

2.脚本执行完成之后，执行csscan符集扫描工具，并查看输出报告

$ csscan system/manager


Character Set Scanner v1.1 : Release 9.2.0.1.0 - Production on Tue May 27 10:51:57 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production

(1)Full database, (2)User, (3)Table: 1 > 2

Current database character set is WE8ISO8859P1.

Enter new database character set name: > WE8ISO8859P1

Enter array fetch buffer size: 10240 > 1024000 

Enter number of scan processes to utilize(1..32): 1 > 2

Enter user name to scan: > scott

Enumerating tables to scan... --开始扫描，如果数据量大，会很慢

. process 1 scanning SCOTT.EMP[AAAi+sAABAAALKZAAA]
. process 1 scanning SCOTT.DEPT[AAAi+tAABAAALKpAAA]
. process 1 scanning SCOTT.BONUS[AAAi+uAABAAALK5AAA]

Creating Database Scan Summary Report...

Creating Individual Exception Report...

Scanner terminated successfully.
$ 
$ ls -otr scan*
-rw-r--r--   1 oracle      5803 May 27 11:06 scan.txt
-rw-r--r--   1 oracle       869 May 27 11:06 scan.out
-rw-r--r--   1 oracle      1117 May 27 11:06 scan.err
$ 
$ more scan.err
Database Scan Individual Exception Report


[Database Scan Parameters]

Parameter                      Value                                           
------------------------------ ------------------------------------------------
Scan type                      User tables                                     
User name                      scott                                           
Scan CHAR data?                YES                                             
Current database character set WE8ISO8859P1                                    
New database character set     WE8ISO8859P1                                    
Scan NCHAR data?               NO                                              
Array fetch buffer size        1024000                                         
Number of processes            2                                               
Capture convertible data?      NO                                              
------------------------------ ------------------------------------------------

[Data Dictionary individual exceptions]


[Application data individual exceptions]

$ 
$ more scan.txt
Database Scan Summary Report

Time Started : 2008-05-27 11:06:02
Time Completed: 2008-05-27 11:06:06

Process ID         Time Started       Time Completed
---------- -------------------- --------------------
         1 2008-05-27 11:06:06 2008-05-27 11:06:06
---------- -------------------- --------------------

[Database Size]

Tablespace                            Used(MB)        Free(MB)       Total(MB)
------------------------------ --------------- --------------- ---------------
SYSTEM                                 346.000          54.000         400.000
TOOLS                                  112.000           5.000         117.000
......

3.最后看看它的帮助说明

$ csscan -help


Character Set Scanner v1.1 : Release 9.2.0.1.0 - Production on Tue May 27 11:10:00 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


You can let Scanner prompt you for parameters by entering the CSSCAN      
command followed by your username/password:                               
                                                                          
Example: CSSCAN SYSTEM/MANAGER                                          
                                                                          
Or, you can control how Scanner runs by entering the CSSCAN command       
followed by various parameters. To specify parameters, you use keywords: 
                                                                          
Example: CSSCAN SYSTEM/MANAGER FULL=y TOCHAR=utf8 ARRAY=102400 PROCESS=3 
                                                                          
Keyword    Default Prompt Description                                     
---------- ------- ------ -------------------------------------------------
USERID             yes    username/password                               
FULL       N       yes    scan entire database                            
USER               yes    user name of the table to scan                  
TABLE              yes    list of tables to scan                          
EXCLUDE                   list of tables to exclude from scan             
TOCHAR             yes    new database character set name                 
FROMCHAR                  current database character set name             
TONCHAR                   new NCHAR character set name                    
FROMNCHAR                 current NCHAR character set name                
ARRAY      10240   yes    size of array fetch buffer                      
PROCESS    1       yes    number of scan process                          
MAXBLOCKS                 split table if larger than MAXBLOCKS            
CAPTURE    N              capture convertible data                        
SUPPRESS                  suppress error log by N per table               
FEEDBACK                  feedback progress every N rows                  
BOUNDARIES                list of column size boundaries for summary report
LASTRPT    N              generate report of the last database scan       
LOG        scan           base name of log files                          
PARFILE                   parameter file name                             
PRESERVE   N              preserve existing scan results                  
HELP       N              show help screen (this screen)                  
---------- ------- ------ -------------------------------------------------
Scanner terminated successfully.
$

--End--