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



本文转自

http://hi.baidu.com/edeed/blog/item/6c268535b9359a1491ef3956.html