六笨哥
分类： Oracle
2008-04-18 11:30:17
|
|
1: 一、常规方法修改数据库字符集 2: 3: 当前数据库字符集:4: SQL> select name,value$ from props$ where name like '%NLS%'; 5: 6: NAME VALUE$ 7: ------------------------------ ------------------------------ 8: NLS_LANGUAGE AMERICAN 9: NLS_TERRITORY AMERICA 10: NLS_CURRENCY $ 11: NLS_ISO_CURRENCY AMERICA 12: NLS_NUMERIC_CHARACTERS ., 13: NLS_CHARACTERSET US7ASCII 14: ...... 15: 16: 20 rows selected. 17: 18: SQL> conn zwfha/admin 19: Connected.20: SQL> select * from tab; 21: 22: TNAME TABTYPE CLUSTERID23: ------------------------------ ------- ---------- 24: HRMS_EMPINFO_COMPANY TABLE 25: HRMS_EMPINFO_DEPARTMENT TABLE 26: HRMS_EMPINFO_DIRECTOR TABLE 27: HRMS_EMPINFO_EDUCATION TABLE 28: HRMS_EMPINFO_EMPLOYEE TABLE 29: HRMS_EMPINFO_EXPERIENCE TABLE 30: HRMS_EMPINFO_FAMILY TABLE 31: HRMS_EMPINFO_HONOR TABLE 32: HRMS_EMPINFO_JOB TABLE 33: HRMS_EMPINFO_STATUS TABLE 34: HRMS_EMPINFO_TEAM TABLE 35: 36: 11 rows selected. 37: 38: SQL> select name from HRMS_EMPINFO_EMPLOYEE where rownum<3; 39: 40: NAME41: ---------------------------------------- 42: 关鹏燕 43: 周杨英 44: 45: 46: 中文显示是正常的。 47: 关闭数据库，打开到mount状态，准备修改数据库字符集(修改前最好备份一下) 48: 49: SQL> shutdown immediate 50: Database closed. 51: Database dismounted. 52: ORACLE instance shut down.53: SQL> 54: SQL> startup mount 55: ORACLE instance started. 56: 57: Total System Global Area 117440512 bytes 58: Fixed Size 787728 bytes 59: Variable Size 91224816 bytes 60: Database Buffers 25165824 bytes 61: Redo Buffers 262144 bytes62: Database mounted. 63: SQL> 64: SQL> alter system enable restricted session; 65: 66: System altered. 67: 68: SQL> show parameter processes 69: 70: NAME TYPE VALUE 71: ------------------------------------ ----------- -------------------- 72: aq_tm_processes integer 0 73: db_writer_processes integer 1 74: gcs_server_processes integer 0 75: job_queue_processes integer 10 76: log_archive_max_processes integer 2 77: processes integer 150 78: SQL> 79: SQL> alter system set job_queue_processes=0; 80: 81: System altered. 82: 83: SQL> alter system set aq_tm_processes=0; 84: 85: System altered. 86: 87: SQL> alter database open; 88: 89: Database altered. 90: 91: SQL> alter database character set zhs16gbk; 92: alter database character set zhs16gbk 93: *94: ERROR at line 1: 95: ORA-12716: Cannot ALTER DATABASE CHARACTER SET when CLOB data exists 96: 97: ---- 98: alter信息： 99: Wed Jan 16 13:54:36 2008100: SYS.METASTYLESHEET (STYLESHEET) - CLOB populated 101: ORA-12716 signalled during: alter database character set zhs16gbk... 102: ---- 103: 这里参考eygle的修改字符集文章() 104: 105: SQL> truncate table Metastylesheet; 106: 107: Table truncated. 108: 109: SQL> alter database character set zhs16gbk; 110: alter database character set zhs16gbk 111: *112: ERROR at line 1: 113: ORA-12716: Cannot ALTER DATABASE CHARACTER SET when CLOB data exists 114: 115: ---- 116: alert信息： 117: Wed Jan 16 13:57:07 2008118: alter database character set zhs16gbk 119: Wed Jan 16 13:57:07 2008120: SYS.RULE$ (CONDITION) - CLOB populated 121: ORA-12716 signalled during: alter database character set zhs16gbk... 122: ---- 123: 俺修改到这，晕了都。。看来9.2跟10的差别还是很大嘞。。要小心啊。 124: google之 125: 126: 使用internal_convert来修改 127: 128: SQL> alter database character set internal_convert zhs16gbk; 129: 130: Database altered. 131: alert里面可以看到，ORACLE会自动转换含有CLOB字段的表132: ---- 133: alert信息： 134: Wed Jan 16 14:06:12 2008135: alter database character set internal_convert zhs16gbk 136: Wed Jan 16 14:06:15 2008137: Private_strands 7 at log switch 138: Thread 1 advanced to log sequence 38 139: Current log# 2 seq# 38 mem# 0: D:\ORACLE\ORADATA\ORCL\REDO02.LOG 140: Wed Jan 16 14:06:16 2008141: Updating character set in controlfile to ZHS16GBK 142: Synchronizing connection with database character set information 143: Wed Jan 16 14:06:16 2008144: Published database character set on system events channel 145: Wed Jan 16 14:06:16 2008146: All processes have switched to database character set 147: SYS.WRI$_DBU_HWM_METADATA (LOGIC) - CLOB representation altered 148: SYS.WRI$_DBU_FEATURE_METADATA (INST_CHK_LOGIC) - CLOB representation altered 149: SYS.WRI$_DBU_FEATURE_METADATA (USG_DET_LOGIC) - CLOB representation altered 150: SYS.WRI$_DBU_FEATURE_USAGE (FEATURE_INFO) - CLOB representation altered 151: SYS.SCHEDULER$_EVENT_LOG (ADDITIONAL_INFO) - CLOB representation altered 152: SYS.RULE$ (CONDITION) - CLOB representation altered 153: Refreshing type attributes with new character set information 154: Completed: alter database character set internal_convert zhs1 155: ---- 156: 157: 因为前面清空了SYS.METASTYLESHEET表，需要重新创建158: 9.2通过@?/rdbms/admin/catmet.sql创建; 159: 10g中没有catmet.sql这个脚本，通过运行catmeta.sql脚本来重建160: @?/rdbms/admin/catmeta.sql 161: (注意这个地方有待商榷，不确定，最好不要使用这种方法修改) 162: 163: shutdown 164: startup 165: 166: 167: SQL> select name from HRMS_EMPINFO_EMPLOYEE where rownum<3; 168: 169: NAME170: ---------------------------------------- 171: 关鹏燕 172: 周杨英 173: 174: SQL> create table t(name varchar2(200)); 175: 176: Table created. 177: 178: SQL> insert into t values('参加中国人民'); 179: 180: 1 row created. 181: 182: SQL> commit; 183: 184: Commit complete. 185: 186: SQL> select * from t; 187: 188: NAME189: ---------------------------------------- 190: 参加中国人民 191: 192: 193: 至此，修改正常。 194: 195: 196: 二、使用internal_convert修改数据库字符集 197: 198: SQL> startup mount 199: ORACLE instance started. 200: 201: Total System Global Area 117440512 bytes 202: Fixed Size 787728 bytes 203: Variable Size 91224816 bytes 204: Database Buffers 25165824 bytes 205: Redo Buffers 262144 bytes206: Database mounted. 207: SQL> show parameter processes 208: 209: NAME TYPE VALUE 210: ------------------------------------ ----------- -------------------------- 211: aq_tm_processes integer 10 212: db_writer_processes integer 1 213: gcs_server_processes integer 0 214: job_queue_processes integer 10 215: log_archive_max_processes integer 2 216: processes integer 150 217: 218: 219: 打开数据库到restricted状态 220: 221: SQL> alter system enable restricted session; 222: 223: System altered. 224: 225: SQL> alter database open; 226: 227: Database altered. 228: 229: 查看当前数据库字符集:230: SQL> col name for a30 231: SQL> col value$ for a30 232: SQL> select name,value$ from props$ where name like '%NLS%'; 233: 234: NAME VALUE$ 235: ------------------------------ ------------------------------ 236: NLS_LANGUAGE AMERICAN 237: NLS_TERRITORY AMERICA 238: NLS_CURRENCY $ 239: NLS_ISO_CURRENCY AMERICA 240: NLS_NUMERIC_CHARACTERS ., 241: NLS_CHARACTERSET US7ASCII 242: ...... 243: 244: 20 rows selected. 245: 246: 这里为了方便区分trace文件名，把trace文件加上标识 247: 248: SQL> alter session set tracefile_identifier='cs'; 249: 250: Session altered. 251: 252: SQL> alter session set sql_trace=true; 253: 254: Session altered. 255: 256: SQL> alter database character set internal_convert zhs16gbk; 257: 258: Database altered. 259: 260: alert信息: 261: ~~~~~~~~~~~~~~~~~~~~~ 262: Wed Jan 16 14:49:47 2008263: alter database character set internal_convert zhs16gbk 264: Wed Jan 16 14:49:50 2008265: Private_strands 7 at log switch 266: Thread 1 advanced to log sequence 38 267: Current log# 2 seq# 38 mem# 0: D:\ORACLE\ORADATA\ORCL\REDO02.LOG 268: Wed Jan 16 14:49:52 2008269: Updating character set in controlfile to ZHS16GBK 270: Synchronizing connection with database character set information 271: Wed Jan 16 14:49:52 2008272: Published database character set on system events channel 273: SYS.WRI$_DBU_HWM_METADATA (LOGIC) - CLOB representation altered 274: SYS.WRI$_DBU_FEATURE_METADATA (INST_CHK_LOGIC) - CLOB representation altered 275: SYS.WRI$_DBU_FEATURE_METADATA (USG_DET_LOGIC) - CLOB representation altered 276: SYS.WRI$_DBU_FEATURE_USAGE (FEATURE_INFO) - CLOB representation altered 277: SYS.SCHEDULER$_EVENT_LOG (ADDITIONAL_INFO) - CLOB representation altered 278: SYS.RULE$ (CONDITION) - CLOB representation altered 279: SYS.METASTYLESHEET (STYLESHEET) - CLOB representation altered 280: Refreshing type attributes with new character set information 281: 282: alert中的信息与上面的相同，ORACLE在内部转换CLOB字段相关的表为新字符集，这个在trace文件中可以很清楚得看到。 283: 这里就不贴trace信息了。有兴趣自己trace一下。 284: 285: 查看修改后的字符集：286: SQL> select name,value$ from props$ where name like '%NLS%'; 287: 288: NAME VALUE$ 289: ------------------------------ ------------------------------ 290: NLS_LANGUAGE AMERICAN 291: NLS_TERRITORY AMERICA 292: NLS_CURRENCY $ 293: NLS_ISO_CURRENCY AMERICA 294: NLS_NUMERIC_CHARACTERS ., 295: NLS_CHARACTERSET ZHS16GBK 296: ...... 297: 298: SQL> alter system disable restricted session; 299: 300: System altered. 301: 302: D:\>set NLS_LANG=american_america.ZHS16GBK 303: 304: D:\>305: D:\>sqlplus zwfha/admin 306: SQL> select name from HRMS_EMPINFO_JOB where rownum<3; 307: 308: NAME309: ---------------------------------------- 310: 总裁 311: 总监 312: 313: SQL> create table t(name varchar2(200)); 314: 315: Table created. 316: 317: SQL> 318: SQL> insert into t values('在过程里就是吧条件用参数传入'); 319: 320: 1 row created. 321: 322: SQL> commit; 323: 324: Commit complete. 325: 326: SQL> select * from t; 327: 328: NAME329: ---------------------------------------- 330: 在过程里就是吧条件用参数传入 331: 332: 到此，字符集修改完成。这个与上面修改属于同一类型。只不过更加简单明了而已. 333: 334: 三、 335: 参考ITPUB：336: http://www.itpub.net/redirect.php?tid=917943&goto=lastpost |
