不同字符集间的转换，可以使用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
评论
0 点赞
