--Oracle所支持的字符集及分类
Oracle支持两百多种字符集,包含了单字节、可变字节以及通用字符集等。
字符集通常根据说使用的字节数来分类,通常分为以下几类:
1、单字节字符集,如US7ASCII(7bit),WE8ISO8859P1(8bit),WE8DEC(8bit)
2、可变长度字符集,如JA16SJIS,ZHT16HKSCS
3、通用字符集,如AL32UTF8
--注意概念:字符集、编码
Oracle数据库支持的Unicode字符集
Character Set Unicode Encoding Database Character Set National Character Set
UTF8 UTF-8 Yes Yes(Oracle 9i and 10g only)
AL32UTF8 UTF-8 Yes No
AL16UTF16 UTF-16 No Yes
--NLS_LANG
1、NLS_LANG组成
NLS_LANG=_.
例如:NLS_LANG=AMERICAN_AMERICA.AL32UTF8
AMERICAN 是语言, AMERICA 是地区, AL32UTF8 是字符集
常用中文字符集 NLS_LANG="SIMPLIFIED CHINESE_CHINA".ZHS16GBK
常用 unicode 字符集 NLS_LANG="AMERICAN_AMERICA".AL32UTF8
2、客户端 NLS_LANG
Windows:
# 常用中文字符集
set NLS_LANG=SIMPLIFIED CHINESE_CHINA.ZHS16GBK
# 常用unicode字符集
set NLS_LANG=american_america.AL32UTF8
可以通过修改注册表键值永久设置
HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE/HOMExx/NLS_LANG
Unix:
# 常用unicode字符集
export NLS_LANG=american_america.AL32UTF8
# 常用中文字符集
export NLS_LANG="Simplified Chinese_china".ZHS16GBK
可以编辑 bash_profile 文件进行永久设置
vi .bash_profile
NLS_LANG="Simplified Chinese_china".ZHS16GBK
export NLS_LANG
# 使 bash_profile 设置生效
source .bash_profile
3、数据库 NLS_LANG
查看:
SYS@ test11g> col parameter for a30
SYS@ test11g> col value for a30
SYS@ test11g> select * from v$nls_parameters;
PARAMETER VALUE
------------------------------ ------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_CHARACTERSET ZHS16GBK
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
修改:
SQL> startup nomount;
SQL> alter database mount exclusive;
SQL> alter system enable restricted session;
SQL> alter system set job_queue_processes=0;
SQL> alter system set nls_language = "SIMPLIFIED CHINESE" SCOPE=SPFILE;
SQL> alter system set nls_territory = "CHINA" SCOPE=SPFILE;
SQL> alter database open;
SQL> alter database character set ZHS16GBK;
字符集必须是原字符集的超级。
或者强制转换
alter database character set internal_use ZHS16GBK;
SQL> alter system disable restricted session;
# 查询数据库可用字符集
select * from v$nls_valid_values where parameter='CHARACTERSET';
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/27633655/viewspace-1081562/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/27633655/viewspace-1081562/