oracle引用css报错,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

参考：