SQL> select userenv('language') from dual;
USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.WE8ISO8859P1
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
SQL>
SQL> conn roger/roger
Connected.
SQL> create table ht01(id number,name varchar2(6));
Table created.
SQL> insert into ht01 values(1,'海天');
1 row created.
SQL> insert into ht01 values(2,'北京');
1 row created.
SQL> commit;
Commit complete.
SQL>
SQL> select * from ht01;
ID NAME
---------- ------
1 ????
2 ????
SQL>
安装配置csscan
SQL> @?/rdbms/admin/csminst.sql
grant READ on directory log_file_dir to system
*
ERROR at line 1:
ORA-22930: directory does not exist
grant READ on directory data_file_dir to system
*
ERROR at line 1:
ORA-22930: directory does not exist
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: csmig
old 1: create user csmig identified by &csmig_passwd
new 1: create user csmig identified by csmig
User created.
Grant succeeded.
Grant succeeded.
Grant succeeded.
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.
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.
View created.
View created.
View created.
Grant succeeded.
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
[oracle@roger ~]$
从上面的安装结果输出信息来看，下面的2条授权语句执行失败了，
grant READ on directory log_file_dir to system
grant READ on directory data_file_dir to system
这两句授权的语句，是没什么用的，完全可以从scminst.sql脚本中删除，大家可以参考如下metalink文档：
Installing and configuring Csscan in 10g and 11g (Database Character Set Scanner) [ID 745809.1]
关于csscan的运用，大家可以查看帮助信息，可以进行全库，用户等级别的扫描，我这里使用全库扫描。
[oracle@roger ~]$ csscan system/oracle full=y FROMCHAR=WE8ISO8859P1 TOCHAR=ZHS16GBK ARRAY=1024000 PROCESS=1
Character Set Scanner v2.1 : Release 10.2.0.3.0 - Production on Mon Jul 4 16:29:41 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
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
Enumerating tables to scan...
. process 1 scanning SYS.SOURCE$[AAAABIAABAAAAIRAAA]
. process 1 scanning SYS.METHOD$[AAAAC1AABAAAAURAAA]
. process 1 scanning SYS.TYPE$[AAAAC1AABAAAAURAAA]
. process 1 scanning SYS.PARAMETER$[AAAAC1AABAAAAURAAA]
. process 1 scanning SYS.ATTRIBUTE$[AAAAC1AABAAAAURAAA]
. process 1 scanning SYS.TAB$[AAAAACAABAAAAAZAAA]
. process 1 scanning SYS.ARGUMENT$[AAAABHAABAAAAIJAAA]
. process 1 scanning SYS.LIBRARY$[AAAAACAABAAAAAZAAA]
. process 1 scanning SYS.NTAB$[AAAAACAABAAAAAZAAA]
. process 1 scanning SYS.VIEWTRCOL$[AAAAACAABAAAAAZAAA]
. process 1 scanning SYS.TYPE_MISC$[AAAAACAABAAAAAZAAA]
. process 1 scanning SYS.IND$[AAAAACAABAAAAAZAAA]
. process 1 scanning SYS.CLU$[AAAAACAABAAAAAZAAA]
. process 1 scanning SYS.OPQTYPE$[AAAAACAABAAAAAZAAA]
. process 1 scanning SYS.COL$[AAAAACAABAAAAAZAAA]
. process 1 scanning SYS.ICOL$[AAAAACAABAAAAAZAAA]
. process 1 scanning SYS.LOB$[AAAAACAABAAAAAZAAA]
. process 1 scanning SYS.ATTRCOL$[AAAAACAABAAAAAZAAA]
. process 1 scanning SYS.OBJ$[AAAAASAABAAAAB5AAA]
. process 1 scanning SYS.VIEW$[AAAAA/AABAAAAHJAAA]
. process 1 scanning SYS.HISTGRM$[AAAAD7AABAAAAcJAAA]
. process 1 scanning MDSYS.SDO_CS_SRS[AAAMAAAADAAADcRAAA]
. process 1 scanning SYS.JAVASNM$[AAAAGHAABAAAAvZAAA]
. process 1 scanning MDSYS.SDO_COORD_REF_SYS[AAALVOAADAAADfJAAA]
. process 1 scanning SYS.SETTINGS$[AAAABQAABAAAAJRAAA]
. process 1 scanning SYS.IDL_CHAR$[AAAABKAABAAAAIhAAA]
. process 1 scanning SYS.SYS$SERVICE_METRICS_TAB[AAACZYAADAAABVhAAA]
. process 1 scanning SYS.SYN$[AAAAA+AABAAAAHBAAA]
. process 1 scanning SYS.WRH$_SQL_PLAN[AAACMpAADAAAAvJAAA]
. process 1 scanning SYS.PROCEDUREINFO$[AAAABGAABAAAAIBAAA]
. process 1 scanning SYS.CDEF$[AAAAAdAABAAAACxAAA]
. process 1 scanning SYS.COM$[AAAABhAABAAAALZAAA]
. process 1 scanning SYS.METASCRIPTFILTER$[AAAAH0AABAAAA95AAA]
. process 1 scanning SYS.CCOL$[AAAAAdAABAAAACxAAA]
. process 1 scanning XDB.XDB$ELEMENT[AAAKP5AADAAACqZAAA]
. process 1 scanning SYS.WRH$_SQLSTAT[AAAMnXAADAAAAsJAAA]
. process 1 scanning SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY[AAABB+AADAAAAUhAAA]
. process 1 scanning SYS.AW_OBJ$[AAAAJLAADAAAAAJAAA]
. process 1 scanning MDSYS.SDO_COORD_OPS[AAALVWAADAAADgBAAA]
. process 1 scanning MDSYS.SDO_COORD_OP_PARAM_VALS[AAALVgAADAAADg5AAA]
. process 1 scanning SYS.TRIGGER$[AAAABRAABAAAAJZAAA]
. process 1 scanning SYS.METAFILTER$[AAAAHpAABAAAA8hAAA]
. process 1 scanning SYS.WRH$_SQLTEXT[AAACMjAADAAAAuZAAA]
. process 1 scanning SYS.METANAMETRANS$[AAAAH3AABAAAA+RAAA]
. process 1 scanning SYS.JAVAOBJ$[AAAABOAABAAAAJBAAA]
. process 1 scanning XDB.XDB$COMPLEX_TYPE[AAAKLvAADAAACJJAAA]
. process 1 scanning SYS.WRH$_PARAMETER[AAAMoFAADAAAA1RAAA]
. process 1 scanning SYS.PROCEDUREJAVA$[AAAAFGAABAAAAopAAA]
. process 1 scanning SYS.PROCEDURE$[AAAABFAABAAAAH5AAA]
. process 1 scanning SYS.METASTYLESHEET[AAAAHuAABAAAA9JAAA]
. process 1 scanning SYS.CON$[AAAAAcAABAAAACpAAA]
. process 1 scanning SYS.VTABLE$[AAAAFuAABAAAAsxAAA]
. process 1 scanning SYSMAN.MGMT_JOB_STEP_PARAMS[AAAMWOAADAAAGV5AAA]
. process 1 scanning SYS.T1[AAAMprAABAAAO6JAAA]
. process 1 scanning XDB.XDB$SCHEMA[AAAKR3AADAAAC6JAAA]
. process 1 scanning SYS.AW$AWMD[AAALc3AADAAAEJZAAA]
. process 1 scanning SYS.WRH$_ROWCACHE_SUMMARY[AAAMn5AADAAAAzRAAA]
. process 1 scanning SYSMAN.MGMT_POLICY_RULE[AAAMVNAADAAAAt5AAA]
. process 1 scanning SYS.WRI$_OPTSTAT_HISTGRM_HISTORY[AAABCBAADAAAAU5AAA]
. process 1 scanning SYS.WRH$_SQL_BIND_METADATA[AAACMtAADAAAAvpAAA]
. process 1 scanning SYS.WRI$_OPTSTAT_IND_HISTORY[AAABB7AADAAAAUJAAA]
. process 1 scanning XDB.XDB$ATTRIBUTE[AAAKQPAADAAACtJAAA]
. process 1 scanning SYSMAN.MGMT_ECM_SNAPSHOT_MD_COLUMNS[AAAMTgAADAAAGAZAAA]
. process 1 scanning XDB.XDB$SIMPLE_TYPE[AAAKJhAADAAAB3ZAAA]
. process 1 scanning SYS.WRH$_EVENT_NAME[AAACM3AADAAAAwpAAA]
. process 1 scanning SYS.METAPATHMAP$[AAAAH6AABAAAA+pAAA]
. process 1 scanning SYSMAN.MGMT_SYSTEM_PERFORMANCE_LOG[AAAMX/AADAAAGj5AAA]
. process 1 scanning SYSMAN.MGMT_JOB_EXECPLAN[AAAMWMAADAAAGVpAAA]
. process 1 scanning XDB.XDB$SEQUENCE_MODEL[AAAKPhAADAAACnZAAA]
. process 1 scanning SYS.WRH$_PARAMETER_NAME[AAACOnAADAAAAypAAA]
. process 1 scanning MDSYS.SDO_DATUMS[AAALVGAADAAADeJAAA]
. process 1 scanning SYS.ERROR$[AAAABPAABAAAAJJAAA]
. process 1 scanning SYS.PENDING_SUB_SESSIONS$[AAAACWAABAAAARZAAA]
. process 1 scanning SYS.VIEWCON$[AAAADPAABAAAAWhAAA]
. process 1 scanning SYS.DEFSUBPART$[AAAAExAABAAAAmRAAA]
. process 1 scanning SYS.NOEXP$[AAAAFDAABAAAAoRAAA]
. process 1 scanning SYS.DIMLEVEL$[AAAAGnAABAAAAzZAAA]
. process 1 scanning SYS.DIMATTR$[AAAAGvAABAAAA0ZAAA]
. process 1 scanning SYS.HIERLEVEL$[AAAAG0AABAAAA1BAAA]
. process 1 scanning SYS.CONTEXT$[AAAAG/AABAAAA2ZAAA]
. process 1 scanning SYS.LOG$[AAAAImAABAAABERAAA]
. process 1 scanning SYS.AUX_STATS$[AAAAI8AABAAABG5AAA]
. process 1 scanning SYS.MAP_FILE_EXTENT$[AAAAJYAABAAABIpAAA]
. process 1 scanning SYS.MAP_EXTELEMENT$[AAAAJbAABAAABJBAAA]
. process 1 scanning SYS.STREAMS$_CAPTURE_PROCESS[AAAAJfAABAAABJRAAA]
. process 1 scanning SYS.STREAMS$_PROCESS_PARAMS[AAAAJqAABAAABKhAAA]
. process 1 scanning SYS.RESOURCE_PLAN$[AAAAMhAABAAABbJAAA]
. process 1 scanning SYS.REGISTRY$[AAAAN8AABAAABexAAA]
. process 1 scanning SYS.REGISTRY$SCHEMAS[AAAAN+AABAAABfBAAA]
. process 1 scanning SYS.AUDIT_ACTIONS[AAAArrAABAAABnhAAA]
. process 1 scanning SYSTEM.MVIEW$_ADV_BASETABLE[AAAA5DAABAAABr5AAA]
. process 1 scanning SYSTEM.MVIEW$_ADV_JOURNAL[AAAA5lAABAAAB0RAAA]
. process 1 scanning SYSTEM.MVIEW$_ADV_PLAN[AAAA5nAABAAAB0hAAA]
. process 1 scanning SYS.DBMS_LOCK_ALLOCATED[AAABEpAABAAACKxAAA]
. process 1 scanning SYS.DBMS_ALERT_INFO[AAABEuAABAAACLBAAA]
. process 1 scanning SYS.ATTRIBUTE_TRANSFORMATIONS$[AAABFjAABAAACMBAAA]
. process 1 scanning SYS.REC_TAB$[AAABGJAABAAACQxAAA]
. process 1 scanning SYSTEM.DEF$_LOB[AAABNGAABAAACnRAAA]
. process 1 scanning SYSTEM.DEF$_PUSHED_TRANSACTIONS[AAABNcAABAAACppAAA]
. process 1 scanning SYS.SCHEDULER$_PROGRAM[AAABPkAABAAACqZAAA]
. process 1 scanning SYS.SCHEDULER$_JOBQTAB[AAABPyAABAAACrBAAA]
. process 1 scanning SYS.LOGMNRG_SEED$[AAABX8AABAAADJJAAA]
. process 1 scanning SYS.LOGMNRG_TS$[AAABYCAABAAADJ5AAA]
. process 1 scanning SYSTEM.REPCAT$_AUDIT_ATTRIBUTE[AAAB9YAABAAAEaZAAA]
. process 1 scanning SYSTEM.REPCAT$_TEMPLATE_TYPES[AAAB+IAABAAAEcBAAA]
. process 1 scanning SYSTEM.REPCAT$_TEMPLATE_REFGROUPS[AAAB+hAABAAAEdZAAA]
. process 1 scanning SYSTEM.REPCAT$_RUNTIME_PARMS[AAAB/eAABAAAEhJAAA]
. process 1 scanning SYSTEM.REPCAT$_INSTANTIATION_DDL[AAAB/wAABAAAEihAAA]
. process 1 scanning SYSTEM.REPCAT$_SITES_NEW[AAAB/4AABAAAEjZAAA]
. process 1 scanning SYS.HS$_BASE_DD[AAACF7AABAAAFGxAAA]
. process 1 scanning SYS.AURORA$STARTUP$CLASSES$[AAAJHJAABAAAKyRAAA]
. process 1 scanning SYS.EPG$_AUTH[AAAKZNAABAAAMLpAAA]
. process 1 scanning SYS.OLAP_OLEDB_FUNCTIONS_PVT[AAAL/SAABAAANGBAAA]
. process 1 scanning SYS.APPLY$_ERROR[AAAAKVAADAAAABxAAA]
. process 1 scanning SYS.DIR$NODE_ATTRIBUTES[AAAAMTAADAAAAIJAAA]
. process 1 scanning SYS.WRI$_ADV_SQLW_TABVOL[AAAA6xAADAAAARpAAA]
. process 1 scanning SYS.SCHEDULER$_WINDOW_DETAILS[AAABQvAADAAAAXRAAA]
. process 1 scanning SYSTEM.LOGSTDBY$PLSQL[AAAB2EAADAAAAlZAAA]
. process 1 scanning SYS.WRH$_COMP_IOSTAT[AAACMYAADAAAAtZAAA]
. process 1 scanning SYS.WRH$_OSSTAT_NAME[AAACOlAADAAAA7ZAAA]
. process 1 scanning SYS.WRH$_DLM_MISC_BL[AAACPDAADAAAA+pAAA]
. process 1 scanning SYS.WRH$_BUFFERED_QUEUES[AAACP0AADAAABDhAAA]
. process 1 scanning DBSNMP.MGMT_SNAPSHOT_SQL[AAACX7AADAAABSZAAA]
. process 1 scanning WMSYS.WM$VERSIONED_TABLES_UNDO_CODE[AAACapAADAAABchAAA]
. process 1 scanning WMSYS.WM$CONSTRAINTS_TABLE[AAACf1AADAAABkxAAA]
. process 1 scanning ORDSYS.SI_VALUES_TAB[AAAKcrAADAAADZpAAA]
. process 1 scanning XDB.Folder23_TAB[AAAKWQAADAAADIpAAA]
ORA-00904: "SYS_NC00011$": invalid identifier
CSS-00144: failed to scan table XDB.Folder23_TAB
. process 1 scanning ORDSYS.SI_IMAGE_FORMATS_TAB[AAAKcnAADAAADZJAAA]
......省略部分内容
. process 1 scanning SYS.STREAMS$_APPLY_SPILL_MSGS_PART[AAABezAADAAAAjRAAA]
. process 1 scanning SYS.WRH$_TABLESPACE_STAT[AAAMnAAADAAABQpAAA]
Creating Database Scan Summary Report...
Creating Individual Exception Report...
Scanner terminated successfully.
[oracle@roger ~]$
scan默认会将扫描相关的信息写入到一个试图scm$parameters中，查询如下：
SQL> select * from csm$parameters;
NAME VALUE
------------------------------ --------------------------------------------------
SCANNER_VERSION 5
SCAN_TYPE ALL
SCAN_CHAR YES
TO_CHARSET_NAME ZHS16GBK
FROM_CHARSET_NAME WE8ISO8859P1
SCAN_NCHAR NO
MAX_ARRAY_SIZE 1024000
MAX_ROWS_IN_HEAP 100
NUMBER_OF_PROCESS 1
SUPPRESS_ERROR_LOG_BY -1
INSERT_SUPPRESSED NO
CAPTURE_CONVERTIBLE_DATA NO
SCANNER_SCRIPT NO
SCANNER_PRESERVE NO
MIGRATE_TO_SUPERSET 0
CSLD_ENABLE 0
PREVIOUS_CHARACTER_SET WE8ISO8859P1
PREVIOUS_NCHAR_SET AL16UTF16
TIME_START 2011-07-04 16:29:44
TIME_END 2011-07-04 16:32:44
20 rows selected.
另外补充一下的是，scan默认也会生成几个scan扫描的结果文件，如下：
[oracle@roger ~]$ ls -ltr scan*
-rwxrwxrwx 1 oracle dba 444 Nov 28 2010 scan.sh
-rw-r--r-- 1 oracle dba 8239 Jul 4 16:32 scan.txt
-rw-r--r-- 1 oracle dba 75329 Jul 4 16:32 scan.out
-rw-r--r-- 1 oracle dba 1878 Jul 4 16:32 scan.err
[oracle@roger ~]$ cat scan.err
Database Scan Individual Exception Report
[Database Scan Parameters]
Parameter Value
------------------------------ ------------------------------------------------
CSSCAN Version v2.1
Instance Name roger
Database Version 10.2.0.4.0
Scan type Full database
Scan CHAR data? YES
Database character set WE8ISO8859P1
FROMCHAR WE8ISO8859P1
TOCHAR ZHS16GBK
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]
User : ROGER
Table : HT01
Column: NAME
Type : VARCHAR2(6)
Number of Exceptions : 2
Max Post Conversion Data Size: 8
ROWID Exception Type Size Cell Data(first 30 bytes)
------------------ ------------------ ----- ------------------------------
AAAMrNAAFAAAAAMAAA exceed column size 8 靠靠
AAAMrNAAFAAAAAMAAB exceed column size 8 靠靠
------------------ ------------------ ----- ------------------------------
10g 中如何修改字符集？
对于oracle10g，已经完全跟8i 9i不一样了，不再使用alter database 命令，metalink解释如下:
Using the "ALTER DATABASE CHARACTER SET" command in 8i or 9i and CSALTER in 10g and up.
首先我们来看下scan.txt的内容：
[Scan Summary]
All character type data in the data dictionary are convertible to the new character set
All character type application data are convertible to the new character set
[Data Dictionary Conversion Summary]
Datatype Changeless Convertible Truncation Lossy
--------------------- ---------------- ---------------- ---------------- ----------------
VARCHAR2 1,922,959 0 0 0
CHAR 1,104 0 0 0
LONG 146,008 0 0 0
CLOB 22,544 1,215 0 0
VARRAY 24 0 0 0
--------------------- ---------------- ---------------- ---------------- ----------------
Total 2,092,639 1,215 0 0
Total in percentage 99.942% 0.058% 0.000% 0.000%
The data dictionary can be safely migrated using the CSALTER script
[Application Data Conversion Summary]
Datatype Changeless Convertible Truncation Lossy
--------------------- ---------------- ---------------- ---------------- ----------------
VARCHAR2 28,702 2 0 0
CHAR 0 0 0 0
LONG 0 0 0 0
CLOB 0 0 0 0
VARRAY 1,575 0 0 0
--------------------- ---------------- ---------------- ---------------- ----------------
Total 30,277 2 0 0
Total in percentage 99.993% 0.007% 0.000% 0.000%
[Distribution of Convertible, Truncated and Lossy Data by Table]
USER.TABLE Convertible Truncation Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
MDSYS.SDO_COORD_OP_PARAM_VALS 200 0 0
MDSYS.SDO_GEOR_XMLSCHEMA_TABLE 1 0 0
MDSYS.SDO_STYLES_TABLE 78 0 0
MDSYS.SDO_XML_SCHEMAS 3 0 0
ROGER.HT01 2 0 0
SYS.METASTYLESHEET 80 0 0
SYS.RULE$ 1 0 0
SYS.WRH$_SQLTEXT 381 0 0
SYS.WRH$_SQL_PLAN 347 0 0
SYS.WRI$_DBU_FEATURE_METADATA 98 0 0
SYS.WRI$_DBU_FEATURE_USAGE 7 0 0
SYS.WRI$_DBU_HWM_METADATA 19 0 0
-------------------------------------------------- ---------------- ---------------- ----------------
[Distribution of Convertible, Truncated and Lossy Data by Column]
USER.TABLE|COLUMN Convertible Truncation Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
MDSYS.SDO_COORD_OP_PARAM_VALS|PARAM_VALUE_FILE 200 0 0
MDSYS.SDO_GEOR_XMLSCHEMA_TABLE|XMLSCHEMA 1 0 0
MDSYS.SDO_STYLES_TABLE|DEFINITION 78 0 0
MDSYS.SDO_XML_SCHEMAS|XMLSCHEMA 3 0 0
ROGER.HT01|NAME 2 0 0
SYS.METASTYLESHEET|STYLESHEET 80 0 0
SYS.RULE$|CONDITION 1 0 0
SYS.WRH$_SQLTEXT|SQL_TEXT 381 0 0
SYS.WRH$_SQL_PLAN|OTHER_XML 347 0 0
SYS.WRI$_DBU_FEATURE_METADATA|INST_CHK_LOGIC 12 0 0
SYS.WRI$_DBU_FEATURE_METADATA|USG_DET_LOGIC 86 0 0
SYS.WRI$_DBU_FEATURE_USAGE|FEATURE_INFO 7 0 0
SYS.WRI$_DBU_HWM_METADATA|LOGIC 19 0 0
-------------------------------------------------- ---------------- ---------------- ----------------
[Indexes to be Rebuilt]
USER.INDEX on USER.TABLE(COLUMN)
对于Convertible, Truncated 的对象，我们可以使用exp或expdp导出，然后drop原表，等字符集修改完以后再进行导入。
另外对于lossy的对象，我们可以借助plsql等工具将数据导出，然后手工转换编码。
关于cscan工具扫描以后产生的结果，如何去阅读的问题，以及在10g以后版本中如何安装的问题；
大家可以参考如下两篇文章：
Installing and configuring Csscan in 10g and 11g (Database Character Set Scanner) [ID 745809.1]
Csscan output explained [ID 444701.1]。
10g 以前的版本可以参考下面的文档：
Installing and Configuring Csscan in 8i and 9i (Database Character Set Scanner) [ID 458122.1]
ok 我们继续，由于前面scan的结果中有[Distribution of Convertible, Truncated and Lossy Data by Table]，
所以我们需要先将这部分表进行导出，不然直接进行字符集的转换肯定会不成功，如下：
SQL> @ ?/rdbms/admin/csalter.plb
0 rows created.
Function created.
Function created.
Procedure created.
This script will update the content of the Oracle Data Dictionary.
Please ensure you have a full backup before initiating this procedure.
Would you like to proceed (Y/N)?y
old 6: if (UPPER('&conf') <> 'Y') then
new 6: if (UPPER('y') <> 'Y') then
Checking data validility...
Sorry only one session is allowed to run this script
PL/SQL procedure successfully completed.
Checking or Converting phrase did not finish successfully
No database (national) character set will be altered
CSALTER finished unsuccessfully.
PL/SQL procedure successfully completed.
0 rows deleted.
Function dropped.
Function dropped.
Procedure dropped.
SQL>
1. 将scan.txt中所列出的部分表进行导出；
exp system/oracle file=exp.dmp tables=(MDSYS.SDO_COORD_OP_PARAM_VALS,MDSYS.SDO_GEOR_XMLSCHEMA_TABLE,MDSYS.SDO_STYLES_TABLE,MDSYS.SDO_XML_SCHEMAS
,ROGER.HT01,SYS.METASTYLESHEET,SYS.RULE$,SYS.WRH$_SQLTEXT,SYS.WRH$_SQL_PLAN,SYS.WRI$_DBU_FEATURE_METADATA ,SYS.WRI$_DBU_FEATURE_USAGE
,SYS.WRI$_DBU_HWM_METADATA) buffer=1000000 log=exp.log
2. 在原库中删除该部分对象；
SQL> drop table MDSYS.SDO_COORD_OP_PARAM_VALS;
drop table MDSYS.SDO_GEOR_XMLSCHEMA_TABLE;
drop table MDSYS.SDO_STYLES_TABLE;
drop table MDSYS.SDO_XML_SCHEMAS;
drop table ROGER.HT01;
drop table SYS.METASTYLESHEET;
drop table SYS.RULE$;
drop table SYS.WRH$_SQLTEXT;
drop table SYS.WRH$_SQL_PLAN;
drop table SYS.WRI$_DBU_FEATURE_METADATA;
drop table SYS.WRI$_DBU_FEATURE_USAGE;
drop table SYS.WRI$_DBU_HWM_METADATA;
Table dropped.
SQL>
Table dropped.
SQL>
Table dropped.
SQL>
Table dropped.
SQL>
Table dropped.
SQL>
Table dropped.
SQL>
Table dropped.
SQL>
Table dropped.
SQL>
Table dropped.
SQL>
Table dropped.
SQL>
Table dropped.
SQL>
Table dropped.
3. 运行csalter修改数据库字符集；
SQL> @ ?/rdbms/admin/csalter.plb;
0 rows created.
Function created.
Function created.
Procedure created.
This script will update the content of the Oracle Data Dictionary.
Please ensure you have a full backup before initiating this procedure.
Would you like to proceed (Y/N)?Y
old 6: if (UPPER('&conf') <> 'Y') then
new 6: if (UPPER('Y') <> 'Y') then
Checking data validility...
begin converting system objects
PL/SQL procedure successfully completed.
Alter the database character set...
CSALTER operation completed, please restart database ---操作完成，需要重启数据库
PL/SQL procedure successfully completed.
0 rows deleted.
Function dropped.
Function dropped.
Procedure dropped.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 230686720 bytes
Fixed Size 1266776 bytes
Variable Size 150997928 bytes
Database Buffers 75497472 bytes
Redo Buffers 2924544 bytes
Database mounted.
Database opened.
SQL> select userenv('language') from dual;
USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.ZHS16GBK
补充一个自己写的简单方案：
我们知道在oracle 10g以前版本，可以通过alter database Character set命令方式来直接修改数据库字符集，但是从oracle10g版本开始，oracle不推荐这样使用，风险很大。
10g 版本，oracle提供了csalter工具进行修改，步骤如下：
一、 对数据库进行全备，可以进行逻辑导出全备或rman全备（记得同时备份参数文件，密码文件以及controlfile）；
二、 安装配置csscan工具；
用具有dba权限的用户执行 @?/rdbms/admin/csminst.sql；
三、 运行csscan进行扫描；
例如：
csscan system/oracle full=y FROMCHAR=WE8ISO8859P1 TOCHAR=ZHS16GBK ARRAY=1024000 PROCESS=1
可以csscan help=y查看帮助
Csscan运行结束后，默认会在当前目前下生存如下3个文件：
[oracle@roger oracle]$ ls -ltr scan*
-rw-r--r-- 1 oracle dba 8239 Jul 4 16:56 scan.txt
-rw-r--r-- 1 oracle dba 73078 Jul 4 16:56 scan.out
-rw-r--r-- 1 oracle dba 1878 Jul 4 16:56 scan.err
Scan工具会把最近一次扫描的参数写入到同义词csm$parameters中，下次进行数据库字符集转换时，直接从该同义词读取信息；
检查scan.txt：
对于convertible, truncated的对象可以通过exp/imp来完成；
对于lossy的对象，可以用plsql进行导出然后手工转换编码，然后删除对象，等运行csalter命令修改字符集完成后，再将前面的对象导入。
导出convetitble和truncate的对象后，需要重新运行csscan工具；
四、 运行csalter修改字符集；
我们知道在oracle 10g以前版本，可以通过alter database Character set命令方式来直接修改数据库字符集，但是从oracle10g版本开始，oracle不推荐这样使用，风险很大。
10g 版本，oracle提供了csalter工具进行修改，步骤如下：
一、 对数据库进行全备，可以进行逻辑导出全备或rman全备（记得同时备份参数文件，密码文件以及controlfile）；
二、 安装配置csscan工具；
用具有dba权限的用户执行 @?/rdbms/admin/csminst.sql；
三、 运行csscan进行扫描；
例如：
csscan system/oracle full=y FROMCHAR=WE8ISO8859P1 TOCHAR=ZHS16GBK ARRAY=1024000 PROCESS=1
可以csscan help=y查看帮助
Csscan运行结束后，默认会在当前目前下生存如下3个文件：
[oracle@roger oracle]$ ls -ltr scan*
-rw-r--r-- 1 oracle dba 8239 Jul 4 16:56 scan.txt
-rw-r--r-- 1 oracle dba 73078 Jul 4 16:56 scan.out
-rw-r--r-- 1 oracle dba 1878 Jul 4 16:56 scan.err
Scan工具会把最近一次扫描的参数写入到同义词csm$parameters中，下次进行数据库字符集转换时，直接从该同义词读取信息；
检查scan.txt：
对于convertible, truncated的对象可以通过exp/imp来完成；
对于lossy的对象，可以用plsql进行导出然后手工转换编码，然后删除对象，等运行csalter命令修改字符集完成后，再将前面的对象导入。
导出convetitble和truncate的对象后，需要重新运行csscan工具；
四、 运行csalter修改字符集；
对于RAC环境：
1. 修改CLUSTER_DATABASE参数为flase；
Alter system set cluster_database=flase scope=spfile；
Alter system set job_queue_processes=0 scope=spfile;
alter system set aq_tm_processes=0 scope=spfile;
2. 启动数据库到单实例模式；
startup restrict
spool switch.log
3. 运行csalter：
---- 以sys执行： @ ?/rdbms/admin/csalter.plb
---- 检查执行csalter的输出信息，是否有错误；
如果执行那么修改还原原始的参数：
Alter system set cluster_database=true scope=spfile；
Alter system set job_queue_processes=old_value scope=both sid=’*’;
alter system set aq_tm_processes=old_value scope=both sid=’*’;
五、 重启数据库；
----shutdown immediate；
----startup
----检查字符集是否修改成功
检查alert log或运行如下sql：
select userenv('language') from dual;
六、 启动另外节点，检查clsuter状态。
七、 检查应用是否正常。
1526
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
