oracle关于字符集的两个小工具

来源：互联网发布：立诺软件编辑：程序博客网时间：2024/04/30 11:49

oracle提供了两个工具用来修改字符集，Database Character Set Scanner(csscan)和Language and Character Set File Scanner(lcsscan)。

下面简单介绍下这两个工具的使用：

一、csscan

csscan是Oracle提供的一个用于检查字符集转换过程中可能会出现的数据丢失或者损坏的情况。也可以单独的扫描某些表某些列能否进行字符集转换，并且能够并行扫描以加快扫描速度。

1.首先以sys用户身份创建用户(CSMIG)和相应的数据字典视图，否则在运行的时候会提示CSS-00107错误：

[oracle@oracle ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Apr 17 16:44:43 2008

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @@?/rdbms/admin/csminst.sql

Grant succeeded.

drop user csmig cascade
*
ERROR at line 1:
ORA-01918: user 'CSMIG' does not exist

Please create password for user CSMIG:
Enter value for csmig_passwd: iloveyou
old 1: create user csmig identified by &csmig_passwd
new 1: create user csmig identified by iloveyou

User created.

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.

Grant succeeded.

Table created.

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

Synonym created.

Grant succeeded.

Table created.

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

Synonym created.

Grant succeeded.

Table created.

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

Synonym created.

Grant succeeded.

Table created.

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

Synonym created.

Grant succeeded.

Table created.

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

Synonym created.

Grant succeeded.

Table created.

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

Synonym created.

Grant succeeded.

Table created.

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

Synonym created.

Grant succeeded.

Table created.

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

Synonym created.

Grant succeeded.

Table created.

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

Synonym created.

Grant succeeded.

Table created.

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

Synonym created.

Grant succeeded.

Table created.

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

Synonym created.

Grant succeeded.

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

Grant succeeded.

Commit complete.

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

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

[oracle@oracle ~]$ csscan system/thunis

Character Set Scanner v2.1 : Release 10.2.0.3.0 - Production on Thu Apr 17 16:50:14 2008

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

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

Current database character set is WE8ISO8859P1.

Enter new database character set name: > WE8ISO8859P1

Enter array fetch buffer size: 1024000 >

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

Enter user name to scan: > hr

Enumerating tables to scan...

. process 1 scanning HR.REGIONS[AAAMgrAAFAAAAAJAAA]
. process 1 scanning HR.EMPLOYEES[AAAMg3AAFAAAABRAAA]
. process 1 scanning HR.DP_TEST[AAAM48AAEAAAAG5AAA]
. process 1 scanning HR.JOB_HISTORY[AAAMg7AAFAAAABpAAA]
. process 1 scanning HR.LOCATIONS[AAAMgvAAFAAAAAhAAA]
. process 1 scanning HR.JOBS[AAAMg1AAFAAAABBAAA]
. process 1 scanning HR.CUSTOMERS[AAAM3mAAEAAAAGBAAA]
. process 1 scanning HR.DEPARTMENTS[AAAMgyAAFAAAAAxAAA]

Creating Database Scan Summary Report...

Creating Individual Exception Report...

Scanner terminated successfully.

[oracle@oracle ~]$ ll -st|grep scan
8 -rw-r--r-- 1 oracle oinstall 1217 Apr 17 16:52 scan.out
8 -rw-r--r-- 1 oracle oinstall 1437 Apr 17 16:52 scan.err
12 -rw-r--r-- 1 oracle oinstall 5561 Apr 17 16:51 scan.txt

[oracle@oracle ~]$ pwd
/home/oracle

[oracle@oracle ~]$ more scan.err
Database Scan Individual Exception Report

[Database Scan Parameters]

Parameter                      Value
------------------------------ ------------------------------------------------
CSSCAN Version                 v2.1
Instance Name                  test
Database Version               10.2.0.4.0
Scan type                      User tables
User name                      hr
Scan CHAR data?                YES
Database character set         WE8ISO8859P1
FROMCHAR                       WE8ISO8859P1
TOCHAR                         WE8ISO8859P1
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]

[oracle@oracle ~]$ more scan.out

Character Set Scanner v2.1 : Release 10.2.0.3.0 - Production on Thu Apr 17 16:50:14 2008

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

(1)Full database, (2)User, (3)Table, (4)Column: 1 >
Current database character set is WE8ISO8859P1.

Enter new database character set name: >
Enter array fetch buffer size: 1024000 >
Enter number of scan processes to utilize(1..32): 1 >

Enter user name to scan: > hr
Enumerating tables to scan...

Creating Database Scan Summary Report...

Creating Individual Exception Report...

Scanner terminated successfully.

[oracle@oracle ~]$ more scan.txt
Database Scan Summary Report

Time Started : 2008-04-17 16:51:24
Time Completed: 2008-04-17 16:51:57

Process ID Time Started Time Completed
---------- -------------------- --------------------
1 2008-04-17 16:51:54 2008-04-17 16:51:55
---------- -------------------- --------------------

[Database Size]

Tablespace                           Used            Free           Total       Expansion
------------------------- --------------- --------------- --------------- ---------------
SYSTEM                            507.06M           2.94M         510.00M            .00K
UNDOTBS1                           13.31M         206.69M         220.00M            .00K
SYSAUX                            267.75M          12.25M         280.00M            .00K
TEMP                                 .00K            .00K            .00K            .00K
USERS                               3.50M           1.50M           5.00M            .00K
EXAMPLE                            68.25M          31.75M         100.00M            .00K
------------------------- --------------- --------------- --------------- ---------------
Total                             859.88M         255.13M       1,115.00M            .00K

[Database Scan Parameters]

[Scan Summary]

All character type application data remain the same in 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                           713                0                0                0
CHAR                                23                0                0                0
LONG                                 0                0                0                0
CLOB                                 0                0                0                0
VARRAY                               0                0                0                0
--------------------- ---------------- ---------------- ---------------- ----------------
Total                              736                0                0                0
Total in percentage            100.000%           0.000%           0.000%           0.000%

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

USER.TABLE Convertible Truncation Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
-------------------------------------------------- ---------------- ---------------- ----------------

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

USER.TABLE|COLUMN Convertible Truncation Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
-------------------------------------------------- ---------------- ---------------- ----------------

[Indexes to be Rebuilt]

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

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

$ csscan -help

Character Set Scanner v2.1 : Release 10.2.0.3.0 - Production on Thu Apr 17 16:40:09 2008

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=1024000 PROCESS=3

Keyword    Default Prompt Description
---------- ------- ------ -------------------------------------------------
USERID             yes    username/password
FULL       N       yes    scan entire database
USER               yes    owner of tables to be scanned
TABLE              yes    list of tables to scan
COLUMN             yes    list of columns 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 national character set name
FROMNCHAR                 current national character set name
ARRAY      1024000 yes    size of array fetch buffer
PROCESS    1       yes    number of concurrent scan process
MAXBLOCKS                 split table if block size exceed MAXBLOCKS
CAPTURE    N              capture convertible data
SUPPRESS                  maximum number of exceptions logged for each table
FEEDBACK                  report 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 file name of report files
PARFILE                   parameter file name
PRESERVE   N              preserve existing scan results
LCSD       N       no     enable language and character set detection
LCSDDATA   LOSSY   no     define the scope of the detection
HELP       N              show help screen (this screen)
QUERY      N              select clause to scan subset of tables or columns
---------- ------- ------ -------------------------------------------------
Scanner terminated successfully.

二、lcsscan

lcsscan用于快速确定指定文本的语言和字符集的基于统计的实用工具。lcsscan的使用相对简单，功能也比较单一。

1.先看看它的帮助说明：

[oracle@oracle ~]$ lcsscan -help

Language and Character Set File Scanner v2.1

You can control how LCSSCAN runs by entering the LCSSCAN command
followed by the required parameters. To specify parameters, you use
keywords:

Example: LCSSCAN RESULTS=2 END=1000 FORMAT=HTML FILE=index.html

Keyword    Description (Default)
--------------------------------------------------------------------
RESULTS    number of language and character set pairs to return (1)
BEGIN      beginning byte offset of file (1)
END        ending byte offset of file (end of file)
FORMAT     file format TEXT, HTML or AUTO detect (TEXT)
FILE       name of input file
HELP       show help screen (this screen)

FILE就是要扫描的文件，BEGIN和END则指定只扫描文件的部分内容。RESULTS指明要返回几组可用的结果，因为一个文件可能兼容于多种字符集。FORMAT指明文件的类型。

2.使用举例：

[oracle@oracle bdump]$ lcsscan file=alert_test.log

Language and Character Set File Scanner v2.1

alert_test.log: ENGLISH US7ASCII;

三、lbuilder

关于oracle的全球化支持，10g提供了一个非常专业的强大的工具Local Builder--lbuilder，它存放在$ORACLE_HOME/nls/lbuilder/文件夹下。lbuilder是一个图形界面的工具，它能够通过自定义语言、区域、字符集以及语言排序等来实现特定的全球化环境。