oracle修改字符集方法

查看源数据库字符集
在sql命令行执行，即可查看
cat exp.dmp |od -x|head -1|awk '{print $2 $3}'|cut -c 3-6
例如我的返回结果为0362，对照以下表
1 0001 US7ASCII
2 0002 WE8DEC
3 0003 WE8HP
4 0004 US8PC437
5 0005 WE8EBCDIC37
6 0006 WE8EBCDIC500
7 0007 WE8EBCDIC1140
8 0008 WE8EBCDIC285
9 0009 WE8EBCDIC1146
10 000A WE8PC850
11 000B D7DEC
12 000C F7DEC
13 000D S7DEC
14 000E E7DEC
15 000F SF7ASCII
16 0010 NDK7DEC
17 0011 I7DEC
18 0012 NL7DEC
19 0013 CH7DEC
20 0014 YUG7ASCII
21 0015 SF7DEC
22 0016 TR7DEC
23 0017 IW7IS960
25 0019 IN8ISCII
27 001B WE8EBCDIC1148
28 001C WE8PC858
31 001F WE8ISO8859P1
32 0020 EE8ISO8859P2
33 0021 SE8ISO8859P3
34 0022 NEE8ISO8859P4
35 0023 CL8ISO8859P5
36 0024 AR8ISO8859P6
37 0025 EL8ISO8859P7
38 0026 IW8ISO8859P8
39 0027 WE8ISO8859P9
40 0028 NE8ISO8859P10
41 0029 TH8TISASCII
42 002A TH8TISEBCDIC
43 002B BN8BSCII
44 002C VN8VN3
45 002D VN8MSWIN1258
46 002E WE8ISO8859P15
47 002F BLT8ISO8859P13
48 0030 CEL8ISO8859P14
49 0031 CL8ISOIR111
50 0032 WE8NEXTSTEP
51 0033 CL8KOI8U
52 0034 AZ8ISO8859P9E
61 003D AR8ASMO708PLUS
70 0046 AR8EBCDICX
72 0048 AR8XBASIC
81 0051 EL8DEC
82 0052 TR8DEC
90 005A WE8EBCDIC37C
91 005B WE8EBCDIC500C
92 005C IW8EBCDIC424
93 005D TR8EBCDIC1026
94 005E WE8EBCDIC871
95 005F WE8EBCDIC284
96 0060 WE8EBCDIC1047
97 0061 WE8EBCDIC1140C
98 0062 WE8EBCDIC1145
99 0063 WE8EBCDIC1148C
100 0064 WE8EBCDIC1047E
101 0065 WE8EBCDIC924
110 006E EEC8EUROASCI
113 0071 EEC8EUROPA3
114 0072 LA8PASSPORT
140 008C BG8PC437S
150 0096 EE8PC852
152 0098 RU8PC866
153 0099 RU8BESTA
154 009A IW8PC1507
155 009B RU8PC855
156 009C TR8PC857
158 009E CL8MACCYRILLIC
159 009F CL8MACCYRILLICS
160 00A0 WE8PC860
161 00A1 IS8PC861
162 00A2 EE8MACCES
163 00A3 EE8MACCROATIANS
164 00A4 TR8MACTURKISHS
165 00A5 IS8MACICELANDICS
166 00A6 EL8MACGREEKS
167 00A7 IW8MACHEBREWS
170 00AA EE8MSWIN1250
171 00AB CL8MSWIN1251
172 00AC ET8MSWIN923
173 00AD BG8MSWIN
174 00AE EL8MSWIN1253
175 00AF IW8MSWIN1255
176 00B0 LT8MSWIN921
177 00B1 TR8MSWIN1254
178 00B2 WE8MSWIN1252
179 00B3 BLT8MSWIN1257
180 00B4 D8EBCDIC273
181 00B5 I8EBCDIC280
182 00B6 DK8EBCDIC277
183 00B7 S8EBCDIC278
184 00B8 EE8EBCDIC870
185 00B9 CL8EBCDIC1025
186 00BA F8EBCDIC297
187 00BB IW8EBCDIC1086
188 00BC CL8EBCDIC1025X
189 00BD D8EBCDIC1141
190 00BE N8PC865
191 00BF BLT8CP921
192 00C0 LV8PC1117
193 00C1 LV8PC8LR
194 00C2 BLT8EBCDIC1112
195 00C3 LV8RST104090
196 00C4 CL8KOI8R
197 00C5 BLT8PC775
198 00C6 DK8EBCDIC1142
199 00C7 S8EBCDIC1143
200 00C8 I8EBCDIC1144
201 00C9 F7SIEMENS9780X
202 00CA E7SIEMENS9780X
203 00CB S7SIEMENS9780X
204 00CC DK7SIEMENS9780X
205 00CD N7SIEMENS9780X
206 00CE I7SIEMENS9780X
207 00CF D7SIEMENS9780X
208 00D0 F8EBCDIC1147
210 00D2 WE8GCOS7
211 00D3 EL8GCOS7
221 00DD US8BS2000
222 00DE D8BS2000
223 00DF F8BS2000
224 00E0 E8BS2000
225 00E1 DK8BS2000
226 00E2 S8BS2000
230 00E6 WE8BS2000E
231 00E7 WE8BS2000
232 00E8 EE8BS2000
233 00E9 CE8BS2000
235 00EB CL8BS2000
239 00EF WE8BS2000L5
241 00F1 WE8DG
251 00FB WE8NCR4970
261 0105 WE8ROMAN8
262 0106 EE8MACCE
263 0107 EE8MACCROATIAN
264 0108 TR8MACTURKISH
265 0109 IS8MACICELANDIC
266 010A EL8MACGREEK
267 010B IW8MACHEBREW
277 0115 US8ICL
278 0116 WE8ICL
279 0117 WE8ISOICLUK
301 012D EE8EBCDIC870C
311 0137 EL8EBCDIC875S
312 0138 TR8EBCDIC1026S
314 013A BLT8EBCDIC1112S
315 013B IW8EBCDIC424S
316 013C EE8EBCDIC870S
317 013D CL8EBCDIC1025S
319 013F TH8TISEBCDICS
320 0140 AR8EBCDIC420S
322 0142 CL8EBCDIC1025C
323 0143 CL8EBCDIC1025R
324 0144 EL8EBCDIC875R
325 0145 CL8EBCDIC1158
326 0146 CL8EBCDIC1158R
327 0147 EL8EBCDIC423R
351 015F WE8MACROMAN8
352 0160 WE8MACROMAN8S
353 0161 TH8MACTHAI
354 0162 TH8MACTHAIS
368 0170 HU8CWI2
380 017C EL8PC437S
381 017D EL8EBCDIC875
382 017E EL8PC737
383 017F LT8PC772
384 0180 LT8PC774
385 0181 EL8PC869
386 0182 EL8PC851
390 0186 CDN8PC863
401 0191 HU8ABMOD
500 01F4 AR8ASMO8X
504 01F8 AR8NAFITHA711T
505 01F9 AR8SAKHR707T
506 01FA AR8MUSSAD768T
507 01FB AR8ADOS710T
508 01FC AR8ADOS720T
509 01FD AR8APTEC715T
511 01FF AR8NAFITHA721T
514 0202 AR8HPARABIC8T
554 022A AR8NAFITHA711
555 022B AR8SAKHR707
556 022C AR8MUSSAD768
557 022D AR8ADOS710
558 022E AR8ADOS720
559 022F AR8APTEC715
560 0230 AR8MSWIN1256
561 0231 AR8NAFITHA721
563 0233 AR8SAKHR706
565 0235 AR8ARABICMAC
566 0236 AR8ARABICMACS
567 0237 AR8ARABICMACT
590 024E LA8ISO6937
798 031E WE8DECTST
829 033D JA16VMS
830 033E JA16EUC
831 033F JA16EUCYEN
832 0340 JA16SJIS
833 0341 JA16DBCS
834 0342 JA16SJISYEN
835 0343 JA16EBCDIC930
836 0344 JA16MACSJIS
837 0345 JA16EUCTILDE
838 0346 JA16SJISTILDE
840 0348 KO16KSC5601
842 034A KO16DBCS
845 034D KO16KSCCS
846 034E KO16MSWIN949
850 0352 ZHS16CGB231280
851 0353 ZHS16MACCGB231280
852 0354 ZHS16GBK
853 0355 ZHS16DBCS
854 0356 ZHS32GB18030
860 035C ZHT32EUC
861 035D ZHT32SOPS
862 035E ZHT16DBT
863 035F ZHT32TRIS
864 0360 ZHT16DBCS
865 0361 ZHT16BIG5
866 0362 ZHT16CCDC
867 0363 ZHT16MSWIN950
868 0364 ZHT16HKSCS
870 0366 AL24UTFFSS
871 0367 UTF8
872 0368 UTFE
873 0369 AL32UTF8
992 03E0 ZHT16HKSCS31
993 03E1 ZHT32EUCTST
994 03E2 WE16DECTST2
995 03E3 WE16DECTST
996 03E4 KO16TSTSET
997 03E5 JA16TSTSET2
998 03E6 JA16TSTSET
1000 03E8 UTF16
1001 03E9 US16TSTFIXED
1002 03EA TIMESTEN8
1830 0726 JA16EUCFIXED
1832 0728 JA16SJISFIXED
1833 0729 JA16DBCSFIXED
1840 0730 KO16KSC5601FIXED
1842 0732 KO16DBCSFIXED
1850 073A ZHS16CGB231280FIXED
1852 073C ZHS16GBKFIXED
1853 073D ZHS16DBCSFIXED
1860 0744 ZHT32EUCFIXED
1863 0747 ZHT32TRISFIXED
1864 0748 ZHT16DBCSFIXED
1865 0749 ZHT16BIG5FIXED
2000 07D0 AL16UTF16
2002 07D2 AL16UTF16LE
9994 270A ISO2022-JP-OUTLOOK
9995 270B ISO2022-JP-OUTLOOK-HWKANA
9996 270C HZ-GB-2312
9997 270D ISO2022-KR
9998 270E ISO2022-CN
9999 270F ISO2022-JP
可知道字符集格式为 ZHT16CCDC
也可以直接在sql命令行执行，即直接返回结果
select nls_charset_name(to_number('0354','xxxx')) from dual;

修改字符集
SQL> conn /as sysdba
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 236000356 bytes
Fixed Size 451684 bytes
Variable Size 201326592 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
System altered.
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
System altered.
SQL> ALTER SYSTEM SET AQ_TM_PROCESSES=0;
System altered.
SQL> alter database open;
Database altered.
SQL> ALTER DATABASE CHARACTER SET ZHS16GBK;
ALTER DATABASE CHARACTER SET ZHS16GBK
*
ERROR at line 1:
ORA-12712: new character set must be a superset of old character set
提示我们的字符集：新字符集必须为旧字符集的超集，这时我们可以跳过超集的检查做更改：
SQL> ALTER DATABASE character set INTERNAL_USE ZHS16GBK;
Database altered.
SQL> select * from v$nls_parameters;
略
19 rows selected.
重启检查是否更改完成：
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 236000356 bytes
Fixed Size 451684 bytes
Variable Size 201326592 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL> select * from v$nls_parameters;