10g中如何修改数据库字符集？

SQL> !uname -a
Linux roger 2.6.9-42.ELsmp #1 SMP Wed Jul 12 23:27:17 EDT 2006 i686 i686 i386 GNU/Linux

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状态。
七、 检查应用是否正常。

posted on 2011-08-28 23:18 Roger's oracle blog 阅读(502) 评论(0) 编辑收藏举报