更改dmp文件中的字符集 -- dmp2utf8-CSDN博客

更改dmp文件中的字符集 -- dmp2utf8

http://www.anysql.net/tools/new_tool_dmp2utf8.html

[@more@]看到过几次"如何修改dmp文件字符集"的问题, 我自已仅在刚入门时, 在老板的指导下用过一次. 到现在也不知道这种直接更新dmp文件第二个和第三个字节的方法到底安全不安全, 因为除了第二个第三个字节外, dmp文件中, 每个字符列都有相应的字符集标识的.

虽然如此, 还是发布一个小工具吧, 可以用于修改dmp中的字符集标识, 新工具命令为: dmp2utf8, 命令使用语法如下:

Usange: dmp2utf8 dmpfile [ charset id]

如果不指定第二个参数, 则默认将改dmp文件为utf-8字符集, 这就是名字的由来.

更安全的做法应当是Clone一个数据库, 然后更新字符集:

ALTER DATABASE CHARACTER SET INTERNAL_USE target_char_set;

然后进行导出, 这样新导出的dmp文件就用新的字符集了. 对于直接修改dmp文件字符集, 有人很清楚吗?

慎重声明: 本站只提供工具, 不负责其他责任, 改dmp文件字符集之前, 请注意备份, 虽然这个程序只会更新第二和第三字节.

查询charset_id的方法:

col nls_charset_id for 9999
col nls_charset_name for a30
col hex_id for a20
select
nls_charset_id(value) nls_charset_id,
value nls_charset_name,
to_char(nls_charset_id(value),'xxxx') hex_id
from v$nls_valid_values
where parameter = 'CHARACTERSET'
order by nls_charset_id(value)
/

NLS_ NLS_CHARSET_NAME HEX_ID
---- ------------------------------ --------------------
1 US7ASCII 1
2 WE8DEC 2
3 WE8HP 3
4 US8PC437 4
5 WE8EBCDIC37 5
6 WE8EBCDIC500 6
7 WE8EBCDIC1140 7
8 WE8EBCDIC285 8
9 WE8EBCDIC1146 9
10 WE8PC850 a
11 D7DEC b
12 F7DEC c
13 S7DEC d
14 E7DEC e
15 SF7ASCII f
16 NDK7DEC 10
17 I7DEC 11
18 NL7DEC 12
19 CH7DEC 13
20 YUG7ASCII 14

NLS_ NLS_CHARSET_NAME HEX_ID
---- ------------------------------ --------------------
21 SF7DEC 15
22 TR7DEC 16
23 IW7IS960 17
25 IN8ISCII 19
27 WE8EBCDIC1148 1b
28 WE8PC858 1c
31 WE8ISO8859P1 1f
32 EE8ISO8859P2 20
33 SE8ISO8859P3 21
34 NEE8ISO8859P4 22
35 CL8ISO8859P5 23
36 AR8ISO8859P6 24
37 EL8ISO8859P7 25
38 IW8ISO8859P8 26
39 WE8ISO8859P9 27
40 NE8ISO8859P10 28
41 TH8TISASCII 29
42 TH8TISEBCDIC 2a
43 BN8BSCII 2b
44 VN8VN3 2c
45 VN8MSWIN1258 2d

NLS_ NLS_CHARSET_NAME HEX_ID
---- ------------------------------ --------------------
46 WE8ISO8859P15 2e
50 WE8NEXTSTEP 32
61 AR8ASMO708PLUS 3d
70 AR8EBCDICX 46
72 AR8XBASIC 48
81 EL8DEC 51
82 TR8DEC 52
90 WE8EBCDIC37C 5a
91 WE8EBCDIC500C 5b
92 IW8EBCDIC424 5c
93 TR8EBCDIC1026 5d
94 WE8EBCDIC871 5e
95 WE8EBCDIC284 5f
96 WE8EBCDIC1047 60
97 WE8EBCDIC1140C 61
98 WE8EBCDIC1145 62
99 WE8EBCDIC1148C 63
110 EEC8EUROASCI 6e
113 EEC8EUROPA3 71
114 LA8PASSPORT 72
140 BG8PC437S 8c

NLS_ NLS_CHARSET_NAME HEX_ID
---- ------------------------------ --------------------
150 EE8PC852 96
152 RU8PC866 98
153 RU8BESTA 99
154 IW8PC1507 9a
155 RU8PC855 9b
156 TR8PC857 9c
158 CL8MACCYRILLIC 9e
159 CL8MACCYRILLICS 9f
160 WE8PC860 a0
161 IS8PC861 a1
162 EE8MACCES a2
163 EE8MACCROATIANS a3
164 TR8MACTURKISHS a4
165 IS8MACICELANDICS a5
166 EL8MACGREEKS a6
167 IW8MACHEBREWS a7
170 EE8MSWIN1250 aa
171 CL8MSWIN1251 ab
172 ET8MSWIN923 ac
173 BG8MSWIN ad
174 EL8MSWIN1253 ae

NLS_ NLS_CHARSET_NAME HEX_ID
---- ------------------------------ --------------------
175 IW8MSWIN1255 af
176 LT8MSWIN921 b0
177 TR8MSWIN1254 b1
178 WE8MSWIN1252 b2
179 BLT8MSWIN1257 b3
180 D8EBCDIC273 b4
181 I8EBCDIC280 b5
182 DK8EBCDIC277 b6
183 S8EBCDIC278 b7
184 EE8EBCDIC870 b8
185 CL8EBCDIC1025 b9
186 F8EBCDIC297 ba
187 IW8EBCDIC1086 bb
188 CL8EBCDIC1025X bc
189 D8EBCDIC1141 bd
190 N8PC865 be
191 BLT8CP921 bf
192 LV8PC1117 c0
193 LV8PC8LR c1
194 BLT8EBCDIC1112 c2
195 LV8RST104090 c3

NLS_ NLS_CHARSET_NAME HEX_ID
---- ------------------------------ --------------------
196 CL8KOI8R c4
197 BLT8PC775 c5
198 DK8EBCDIC1142 c6
199 S8EBCDIC1143 c7
200 I8EBCDIC1144 c8
201 F7SIEMENS9780X c9
202 E7SIEMENS9780X ca
203 S7SIEMENS9780X cb
204 DK7SIEMENS9780X cc
205 N7SIEMENS9780X cd
206 I7SIEMENS9780X ce
207 D7SIEMENS9780X cf
208 F8EBCDIC1147 d0
210 WE8GCOS7 d2
211 EL8GCOS7 d3
221 US8BS2000 dd
222 D8BS2000 de
223 F8BS2000 df
224 E8BS2000 e0
225 DK8BS2000 e1
226 S8BS2000 e2

NLS_ NLS_CHARSET_NAME HEX_ID
---- ------------------------------ --------------------
230 WE8BS2000E e6
231 WE8BS2000 e7
232 EE8BS2000 e8
235 CL8BS2000 eb
239 WE8BS2000L5 ef
241 WE8DG f1
251 WE8NCR4970 fb
261 WE8ROMAN8 105
262 EE8MACCE 106
263 EE8MACCROATIAN 107
264 TR8MACTURKISH 108
265 IS8MACICELANDIC 109
266 EL8MACGREEK 10a
267 IW8MACHEBREW 10b
277 US8ICL 115
278 WE8ICL 116
279 WE8ISOICLUK 117
301 EE8EBCDIC870C 12d
311 EL8EBCDIC875S 137
312 TR8EBCDIC1026S 138
314 BLT8EBCDIC1112S 13a

NLS_ NLS_CHARSET_NAME HEX_ID
---- ------------------------------ --------------------
315 IW8EBCDIC424S 13b
316 EE8EBCDIC870S 13c
317 CL8EBCDIC1025S 13d
319 TH8TISEBCDICS 13f
320 AR8EBCDIC420S 140
322 CL8EBCDIC1025C 142
323 CL8EBCDIC1025R 143
324 EL8EBCDIC875R 144
351 WE8MACROMAN8 15f
352 WE8MACROMAN8S 160
353 TH8MACTHAI 161
354 TH8MACTHAIS 162
368 HU8CWI2 170
380 EL8PC437S 17c
381 EL8EBCDIC875 17d
382 EL8PC737 17e
383 LT8PC772 17f
384 LT8PC774 180
385 EL8PC869 181
386 EL8PC851 182
390 CDN8PC863 186

NLS_ NLS_CHARSET_NAME HEX_ID
---- ------------------------------ --------------------
401 HU8ABMOD 191
500 AR8ASMO8X 1f4
504 AR8NAFITHA711T 1f8
505 AR8SAKHR707T 1f9
506 AR8MUSSAD768T 1fa
507 AR8ADOS710T 1fb
508 AR8ADOS720T 1fc
509 AR8APTEC715T 1fd
511 AR8NAFITHA721T 1ff
514 AR8HPARABIC8T 202
554 AR8NAFITHA711 22a
555 AR8SAKHR707 22b
556 AR8MUSSAD768 22c
557 AR8ADOS710 22d
558 AR8ADOS720 22e
559 AR8APTEC715 22f
560 AR8MSWIN1256 230
560 AR8MSAWIN 230
561 AR8NAFITHA721 231
563 AR8SAKHR706 233
565 AR8ARABICMAC 235

NLS_ NLS_CHARSET_NAME HEX_ID
---- ------------------------------ --------------------
566 AR8ARABICMACS 236
567 AR8ARABICMACT 237
590 LA8ISO6937 24e
798 WE8DECTST 31e
829 JA16VMS 33d
830 JA16EUC 33e
831 JA16EUCYEN 33f
832 JA16SJIS 340
833 JA16DBCS 341
834 JA16SJISYEN 342
835 JA16EBCDIC930 343
836 JA16MACSJIS 344
840 KO16KSC5601 348
842 KO16DBCS 34a
845 KO16KSCCS 34d
846 KO16MSWIN949 34e
850 ZHS16CGB231280 352
851 ZHS16MACCGB231280 353
852 ZHS16GBK 354
853 ZHS16DBCS 355
860 ZHT32EUC 35c

NLS_ NLS_CHARSET_NAME HEX_ID
---- ------------------------------ --------------------
861 ZHT32SOPS 35d
862 ZHT16DBT 35e
863 ZHT32TRIS 35f
864 ZHT16DBCS 360
865 ZHT16BIG5 361
866 ZHT16CCDC 362
867 ZHT16MSWIN950 363
868 ZHT16HKSCS 364
870 AL24UTFFSS 366
871 UTF8 367
872 UTFE 368
994 WE16DECTST2 3e2
995 WE16DECTST 3e3
996 KO16TSTSET 3e4
997 JA16TSTSET2 3e5
998 JA16TSTSET 3e6
1001 US16TSTFIXED 3e9
1830 JA16EUCFIXED 726
1832 JA16SJISFIXED 728
1833 JA16DBCSFIXED 729
1840 KO16KSC5601FIXED 730

NLS_ NLS_CHARSET_NAME HEX_ID
---- ------------------------------ --------------------
1842 KO16DBCSFIXED 732
1850 ZHS16CGB231280FIXED 73a
1852 ZHS16GBKFIXED 73c
1853 ZHS16DBCSFIXED 73d
1860 ZHT32EUCFIXED 744
1863 ZHT32TRISFIXED 747
1864 ZHT16DBCSFIXED 748
1865 ZHT16BIG5FIXED 749
9996 HZ-GB-2312 270c
9997 ISO2022-KR 270d
9998 ISO2022-CN 270e
9999 ISO2022-JP 270f

242 rows selected

SQL> desc v$nls_valid_values
Name Type Nullable Default Comments
--------- ------------ -------- ------- --------
PARAMETER VARCHAR2(64) Y
VALUE VARCHAR2(64) Y

SQL> select distinct parameter from v$nls_valid_values;

PARAMETER
----------------------------------------------------------------
CHARACTERSET
LANGUAGE
SORT
TERRITORY

SQL>