修改Oracle数据库字符集

Java代码  

1. ALTER DATABASE CHARACTER SET ZHS16GBK;  

时提示 

Java代码  

1. ORA-12712: new character set must be a superset of old character set  

后来查了很多资料，可以不理会这这个错误，可以路过超集的检查。 

Java代码  

1. ALTER DATABASE character set INTERNAL_USE ZHS16GBK;  

操作过程如下： 

Java代码  

1. SQL> conn /as sysdba  
2. Connected.  
3. SQL> shutdown immediate;  
4. Database closed.  
5. Database dismounted.  
6. ORACLE instance shut down.  
7. SQL> startup mount  
8. ORACLE instance started.  
9.   
10. Total System Global Area  236000356 bytes  
11. Fixed Size                   451684 bytes  
12. Variable Size             201326592 bytes  
13. Database Buffers           33554432 bytes  
14. Redo Buffers                 667648 bytes  
15. Database mounted.  
16. SQL> ALTER SESSION SET SQL_TRACE=TRUE;//语句跟踪  
17. System altered.  
18. SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;  
19.   
20. System altered.  
21.   
22. SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;  
23.   
24. System altered.  
25.   
26. SQL> ALTER SYSTEM SET AQ_TM_PROCESSES=0;  
27.   
28. System altered.  
29.   
30. SQL> alter database open;  
31.    
32. Database altered.  
33.   
34. SQL> ALTER DATABASE CHARACTER SET ZHS16GBK;  
35. ALTER DATABASE CHARACTER SET ZHS16GBK  
36. *  
37. ERROR at line 1:  
38. ORA-12712: new character set must be a superset of old character set  
39.   
40.   
41. 提示我们的字符集：新字符集必须为旧字符集的超集，这时我们可以跳过超集的检查做更改：  
42.   
43. SQL> ALTER DATABASE character set INTERNAL_USE ZHS16GBK;  
44.   
45. Database altered.  
46.   
47. SQL> select * from v$nls_parameters;  
48.   
49. PARAMETER                                                        VALUE  
50. ---------------------------------------------------------------- ---------------  
51. NLS_LANGUAGE                                                     AMERICAN  
52. NLS_TERRITORY                                                    AMERICA  
53. NLS_CURRENCY                                                     $  
54. NLS_ISO_CURRENCY                                                 AMERICA  
55. NLS_NUMERIC_CHARACTERS                                           .,  
56. NLS_CALENDAR                                                     GREGORIAN  
57. NLS_DATE_FORMAT                                                  DD-MON-RR  
58. NLS_DATE_LANGUAGE                                                AMERICAN  
59. NLS_CHARACTERSET                                                 ZHS16GBK  
60. NLS_SORT                                                         BINARY  
61. NLS_TIME_FORMAT                                                  HH.MI.SSXFF AM  
62.   
63. PARAMETER                                                        VALUE  
64. ---------------------------------------------------------------- ---------------  
65. NLS_TIMESTAMP_FORMAT                                             DD-MON-RR HH.MI  
66. NLS_TIME_TZ_FORMAT                                               HH.MI.SSXFF AM  
67. NLS_TIMESTAMP_TZ_FORMAT                                          DD-MON-RR HH.MI  
68. NLS_DUAL_CURRENCY                                                $  
69. NLS_NCHAR_CHARACTERSET                                           UTF8  
70. NLS_COMP                                                         BINARY  
71. NLS_LENGTH_SEMANTICS                                             BYTE  
72. NLS_NCHAR_CONV_EXCP                                              FALSE  
73.   
74. 19 rows selected.  
75.   
76.   
77. 重启检查是否更改完成：  
78.   
79. SQL> shutdown immediate;  
80. Database closed.  
81. Database dismounted.  
82. ORACLE instance shut down.  
83. SQL> startup  
84. ORACLE instance started.  
85.   
86. Total System Global Area  236000356 bytes  
87. Fixed Size                   451684 bytes  
88. Variable Size             201326592 bytes  
89. Database Buffers           33554432 bytes  
90. Redo Buffers                 667648 bytes  
91. Database mounted.  
92. Database opened.  
93. SQL> select * from v$nls_parameters;  
94.   
95. PARAMETER                                                        VALUE  
96. ---------------------------------------------------------------- ---------------  
97. NLS_LANGUAGE                                                     AMERICAN  
98. NLS_TERRITORY                                                    AMERICA  
99. NLS_CURRENCY                                                     $  
100. NLS_ISO_CURRENCY                                                 AMERICA  
101. NLS_NUMERIC_CHARACTERS                                           .,  
102. NLS_CALENDAR                                                     GREGORIAN  
103. NLS_DATE_FORMAT                                                  DD-MON-RR  
104. NLS_DATE_LANGUAGE                                                AMERICAN  
105. NLS_CHARACTERSET                                                 ZHS16GBK  
106. NLS_SORT                                                         BINARY  
107. NLS_TIME_FORMAT                                                  HH.MI.SSXFF AM  
108.   
109. PARAMETER                                                        VALUE  
110. ---------------------------------------------------------------- ---------------  
111. NLS_TIMESTAMP_FORMAT                                             DD-MON-RR HH.MI  
112. NLS_TIME_TZ_FORMAT                                               HH.MI.SSXFF AM  
113. NLS_TIMESTAMP_TZ_FORMAT                                          DD-MON-RR HH.MI  
114. NLS_DUAL_CURRENCY                                                $  
115. NLS_NCHAR_CHARACTERSET                                           UTF8  
116. NLS_COMP                                                         BINARY  
117. NLS_LENGTH_SEMANTICS                                             BYTE  
118. NLS_NCHAR_CONV_EXCP                                              FALSE  
119.   
120. 19 rows selected.  

另外，网上的很多文章都有个注意： 

引用

在Oracle9i中，如果数据库存在CLOB类型字段，那么就不允许对字符集进行转换 

对于不同情况，Oracle提供不同的解决方案，如果是用户数据表，一般我们可以把包含CLOB字段的表导出，然后drop掉相关对象， 
转换后再导入数据库；对于系统表，可以按照以下方式处理: 

Java代码  

1. SQL> truncate table Metastylesheet;  
2.   
3.  Table truncated.