oracle 字符集修改及子集超级关系

来源：互联网发布：wenger 知乎编辑：程序博客网时间：2024/04/30 18:46

字符集修改有2中办法：

1）原库导出，然后重新建立新字符集的新库，再导入，这里需要注意导出导入客户端字符集。

2）ALTER DATABASE CHARACTER SET步骤如下：

A.备份数据库（一定需要，失败可恢复）

B.关闭数据库shutdown immediate,启动到mount

C.启用restrict session alter system enable resricted session

D.确认没有job运行，没有高可用等传输功能在运行

E.打开数据库 alter database open

F.修改字符集 alter database character set xxx(字符集一定要写正确，否则会报错，需要重新启动才能修改,这里根据alter log错误修正）

H.确认修改成功 select name,value$ from props$ where name like '%NLS%'

可以直接用ALTER DATABASE CHARACTER SET 进行修改字符集，有以下要求：

The command "ALTER DATABASE CHARACTER SET xxxxx" is only work in these cases:

1.the new character set = the old set
2.the old character set = US7ASCII

3.the new character set is the superset of the old set

注意第2点，因为所有的字符集都是US7ASCII的超级，所以可以直接转换为US7ASCII

在oracle 10g版本里的子集超级关系，官方文档里已经非常清晰的说明了，文档如下：

http://docs.oracle.com/cd/B19306_01/server.102/b14225/applocaledata.htm#g681463

而8i,9i的接触不多，转载记录如下：

The following list  descripe the relation between character set and its superset
8.1.6 Subset/Superset Pairs
===========================

A. Current Char set          B. New Char set (Superset of A.)
-------------------          --------------------------------
US7ASCII                      WE8DEC
US7ASCII                      US8PC437
US7ASCII                      WE8PC850
US7ASCII                      IN8ISCII
US7ASCII                      WE8PC858
US7ASCII                      WE8ISO8859P1
US7ASCII                      EE8ISO8859P2
US7ASCII                      SE8ISO8859P3
US7ASCII                      NEE8ISO8859P4
US7ASCII                      CL8ISO8859P5
US7ASCII                      AR8ISO8859P6
US7ASCII                      EL8ISO8859P7
US7ASCII                      IW8ISO8859P8
US7ASCII                      WE8ISO8859P9
US7ASCII                      NE8ISO8859P10
US7ASCII                      TH8TISASCII
US7ASCII                      BN8BSCII
US7ASCII                      VN8VN3
US7ASCII                      VN8MSWIN1258
US7ASCII                      WE8ISO8859P15
US7ASCII                      WE8NEXTSTEP
US7ASCII                      AR8ASMO708PLUS
US7ASCII                      EL8DEC
US7ASCII                      TR8DEC
US7ASCII                      LA8PASSPORT
US7ASCII                      BG8PC437S
US7ASCII                      EE8PC852
US7ASCII                      RU8PC866
US7ASCII                      RU8BESTA
US7ASCII                      IW8PC1507
US7ASCII                      RU8PC855
US7ASCII                      TR8PC857
US7ASCII                      CL8MACCYRILLICS
US7ASCII                      WE8PC860
US7ASCII                      IS8PC861
US7ASCII                      EE8MACCES
US7ASCII                      EE8MACCROATIANS
US7ASCII                      TR8MACTURKISHS
US7ASCII                      EL8MACGREEKS
US7ASCII                      IW8MACHEBREWS
US7ASCII                      EE8MSWIN1250
US7ASCII                      CL8MSWIN1251
US7ASCII                      ET8MSWIN923
US7ASCII                      BG8MSWIN
US7ASCII                      EL8MSWIN1253
US7ASCII                      IW8MSWIN1255
US7ASCII                      LT8MSWIN921
US7ASCII                      TR8MSWIN1254
US7ASCII                      WE8MSWIN1252
US7ASCII                      BLT8MSWIN1257
US7ASCII                      N8PC865
US7ASCII                      BLT8CP921
US7ASCII                      LV8PC1117
US7ASCII                      LV8PC8LR
US7ASCII                      LV8RST104090
US7ASCII                      CL8KOI8R
US7ASCII                      BLT8PC775
US7ASCII                      WE8DG
US7ASCII                      WE8NCR4970
US7ASCII                      WE8ROMAN8
US7ASCII                      WE8MACROMAN8S
US7ASCII                      TH8MACTHAIS
US7ASCII                      HU8CWI2
US7ASCII                      EL8PC437S
US7ASCII                      LT8PC772
US7ASCII                      LT8PC774
US7ASCII                      EL8PC869
US7ASCII                      EL8PC851
US7ASCII                      CDN8PC863
US7ASCII                      HU8ABMOD
US7ASCII                      AR8ASMO8X
US7ASCII                      AR8NAFITHA711T
US7ASCII                      AR8SAKHR707T
US7ASCII                      AR8MUSSAD768T
US7ASCII                      AR8ADOS710T
US7ASCII                      AR8ADOS720T
US7ASCII                      AR8APTEC715T
US7ASCII                      AR8NAFITHA721T
US7ASCII                      AR8HPARABIC8T
US7ASCII                      AR8NAFITHA711
US7ASCII                      AR8SAKHR707
US7ASCII                      AR8MUSSAD768
US7ASCII                      AR8ADOS710
US7ASCII                      AR8ADOS720
US7ASCII                      AR8APTEC715
US7ASCII                      AR8MSAWIN
US7ASCII                      AR8NAFITHA721
US7ASCII                      AR8SAKHR706
US7ASCII                      AR8ARABICMACS
US7ASCII                      LA8ISO6937
US7ASCII                      JA16VMS
US7ASCII                      JA16EUC
US7ASCII                      JA16SJIS
US7ASCII                      KO16KSC5601
US7ASCII                      KO16KSCCS
US7ASCII                      KO16MSWIN949
US7ASCII                      ZHS16CGB231280
US7ASCII                      ZHS16GBK
US7ASCII                      ZHT32EUC
US7ASCII                      ZHT32SOPS
US7ASCII                      ZHT16DBT
US7ASCII                      ZHT32TRIS
US7ASCII                      ZHT16BIG5
US7ASCII                      ZHT16CCDC
US7ASCII                      ZHT16MSWIN950
US7ASCII                      AL24UTFFSS
US7ASCII                      UTF8
US7ASCII                      JA16TSTSET2
US7ASCII                      JA16TSTSET

8.1.7 Additions
===============

US7ASCII                      ZHT16HKSCS
US7ASCII                      KO16TSTSET
WE8DEC                         TR8DEC
WE8DEC                         WE8NCR4970
WE8PC850                      WE8PC858
D7DEC                         D7SIEMENS9780X
I7DEC                         I7SIEMENS9780X
WE8ISO8859P1                   WE8MSWIN1252
AR8ISO8859P6                   AR8ASMO708PLUS
AR8ISO8859P6                   AR8ASMO8X
IW8EBCDIC424                   IW8EBCDIC1086
IW8EBCDIC1086                IW8EBCDIC424
LV8PC8LR                      LV8RST104090
DK7SIEMENS9780X                N7SIEMENS9780X
N7SIEMENS9780X                DK7SIEMENS9780X
I7SIEMENS9780X                I7DEC
D7SIEMENS9780X                D7DEC
WE8NCR4970                   WE8DEC
WE8NCR4970                   TR8DEC
AR8SAKHR707T                   AR8SAKHR707
AR8MUSSAD768T                AR8MUSSAD768
AR8ADOS720T                   AR8ADOS720
AR8NAFITHA711                AR8NAFITHA711T
AR8SAKHR707                   AR8SAKHR707T
AR8MUSSAD768                   AR8MUSSAD768T
AR8ADOS710                   AR8ADOS710T
AR8ADOS720                   AR8ADOS720T
AR8APTEC715                   AR8APTEC715T
AR8NAFITHA721                AR8NAFITHA721T
AR8ARABICMAC                   AR8ARABICMACT
AR8ARABICMACT                AR8ARABICMAC
KO16KSC5601                   KO16MSWIN949
WE16DECTST2                   WE16DECTST
WE16DECTST                   WE16DECTST2


9.0.1 Additions
===============

US7ASCII                      BLT8ISO8859P13
US7ASCII                      CEL8ISO8859P14
US7ASCII                      CL8ISOIR111
US7ASCII                      CL8KOI8U
US7ASCII                      AL32UTF8
BLT8CP921                      BLT8ISO8859P13
US7ASCII                      AR8MSWIN1256

Character Set Subset/Superset Pairs Obsolete from 9.0.1
=======================================================

US7ASCII                      AR8MSAWIN
AR8ARABICMAC                   AR8ARABICMACT