Using Globalization Support
select * from v$nls_valid_values
1.create database
character set
2.some concepts
OS:LANG
NLS_LANG
DB:NLS_LANGUAGE
环境变量(客户端)
NLS_LANG = language_territory.charset
它有三个组成部分（语言、地域和字符集），每个成分控制了NLS子集的特性。其中：
Language指定服务器消息的语言，
territory指定服务器的日期和数字格式，
charset指定字符集。
如：AMERICAN_AMERICA.ZHS16GBK
SIMPLIFIED CHINESE_CHINA.ZHS16GBK
SIMPLIFIED CHINESE_CHINA.al32utf8
SIMPLIFIED CHINESE_CHINA.WE8ISO8859P1
从NLS_LANG的组成我们可以看出，真正影响数据库字符集的其实是第三部分。
所以两个数据库之间的字符集只要第三部分一样就可以相互导入导出数据，
前面影响的只是提示信息是中文还是英文。
win:
C:\>set NLS_LANG=
C:\>echo %NLS_LANG%
%NLS_LANG%
C:\>sqlplus scott/tiger@10g
SQL> select userenv('language') from dual;
SIMPLIFIED CHINESE_CHINA.ZHS16GBK
SQL> select * from test;
1 hello
2 张飞
sql>select * from hh;
第 1 行出现错误:
ORA-00942: table or view does not exist
sql>quit
C:\>set NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
C:\>echo %NLS_LANG%
AMERICAN_AMERICA.ZHS16GBK
C:\>sqlplus scott/tiger@10g
SQL> select * from hh;
ERROR at line 1:
ORA-00942: table or view does not exist
C:\>set NLS_LANG=simplified chinese_china.zhs16gbk
C:\>echo %NLS_LANG%
simplified chinese_china.zhs16gbk
C:\>sqlplus scott/tiger@10g
SQL> select * from hh;
第 1 行出现错误:
ORA-00942: table or view does not exist
NLS_LANG=simplified chinese_china.zhs16gbk
很多人都碰到过因为字符集不同而使数据导入失败的情况。
这涉及三方面的字符集，
一是Oracel server端的字符集，
二是oracle client端的字符集，
三是dmp文件的字符集。在做数据导入的时候，需要这三个字符集都一致才能正确导入。
3.Oracle Server端和Client端的字符集
查询Oracle Server端的字符集
SQL>select userenv('language') from dual;
SIMPLIFIED CHINESE_CHINA.ZHS16GBK
select * from props$
SELECT parameter, value FROM nls_database_parameters WHERE parameter LIKE '%CHARACTERSET%';
查询Oracle client端的字符集:
在Windows平台下，就是注册表里面相应OracleHome的NLS_LANG.
还可以在Dos窗口里面自己设置，比如：set nls_lang=SIMPLIFIED CHINESE_CHINA.ZHS16GBK
在Unix平台下 $echo $NLS_LANG
4.exp imp 时的字符集转换
C:\>set NLS_LANG=simplified chinese_china.zhs16gbk
C:\>exp scott/tiger@10g owner=scott file=scott.dmp
查询dmp文件的字符集:用Oracle的exp工具导出的dmp文件也包含了字符集信息，
dmp文件的第2和第3个字节记录了dmp文件的字符集。
如果dmp文件不大，比如只有几M或几十M，可以用UltraEdit打开（16进制方式），
看第2第3个字节的内容，如0354，然后用以下SQL查出它对应的字符集：
scott:
SQL> select nls_charset_name(to_number('0354','xxxx')) from dual;
ZHS16GBK
如果dmp文件很大，比如有2G以上（这也是最常见的情况），
用文本编辑器打开很慢或者完全打不开，可以用以下命令（在unix主机上）：
cat exp.dmp |od -x|head -1|awk '{print $2 $3}'|cut -c 3-6
然后用上述SQL也可以得到它对应的字符集。
5.不建议修改oracle数据库server端的字符集
特别说明，我们最常用的两种字符集ZHS16GBK(new)和ZHS16CGB231280(字符少,旧)之间不存在子集和超集关系，
因此理论上讲这两种字符集之间的相互转换不受支持。
修改Server端字符集（不建议使用）：
在Oracle 8之前，可以用直接修改数据字典表props$来改变数据库的字符集。
但Oracle8之后，至少有三张系统表记录了数据库字符集的信息，只改props$表并不完全，
可能引起严重的后果。
正确的修改方法如下：
SQL>STARTUP MOUNT;
SQL>ALTER SYSTEM ENABLE RESTRICTED SESSION;
SQL>ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
SQL>ALTER SYSTEM SET AQ_TM_PROCESSES=0;
SQL>ALTER DATABASE OPEN;
SQL>ALTER DATABASE CHARACTER SET ZHS16GBK;
ALTER DATABASE CHARACTER SET internal_convert ZHS16GBK;
SQL>ALTER DATABASE national CHARACTER SET ZHS16GBK;
SQL>SHUTDOWN IMMEDIATE;
SQL>STARTUP
修改dmp文件字符集：
dmp文件的第2第3字节记录了字符集信息，因此直接修改dmp文件的第2第3字节的内容就可以
‘骗’过oracle的检查。这样做理论上也仅是从子集到超集可以修改，但很多情况下在没有
子集和超集关系的情况下也可以修改，我们常用的一些字符集，如US7ASCII，WE8ISO8859P1，
ZHS16CGB231280，ZHS16GBK基本都可以改。因为改的只是dmp文件，所以影响不大。
具体的修改方法比较多，最简单的就是直接用UltraEdit修改dmp文件的第2和第3个字节。
比如想将dmp文件的字符集改为ZHS16GBK，可以用以下SQL查出该种字符集对应的16进制代码：
SQL> select to_char(nls_charset_id('ZHS16GBK'),'xxxx') from dual;
0354
然后将dmp文件的2、3字节修改为0354即可。
如果dmp文件很大，用ue无法打开，就需要用程序的方法了。
6.The Oracle database provides different classes of encoding schemes:
Single-byte
In a single-byte character set, each character occupies one byte.
Single-byte 7-bit encoding schemes can define up to 128 (27) characters; US7ASCII
single-byte 8-bit encoding schemes can define up to 256 (28) characters. 8WE8ISO859P1 WE8DEC EBCDIC I8EBCDIC1144
Varying-width multibyte 变宽的
JA16SJIS
HKSCS-2001(ZHT16HKSCS)
AL32UTF8
ZHS16GBK(1-2 BYTES,英文1,汉字2)
Universal 通用的
AL32UTF8 1, 2, 3, or 4 bytes
AL16UTF16 2 to 4 bytes
utf8 (1-3bytes)
utf16(2bytes)
固定长度
aulutf16
UCS: Universal Character Set 统一的字符集
UTF: UCS Transformation Format UCS的传送格式
例如“汉”字的UCS编码是6C49，可以用4个ascii数字来传输、保存这个编码；
也可以用utf-8编码: 3个连续的字节E6 B1 89来表示它。关键在于通信双方都要认可。
UTF-8、UTF-7、UTF-16都是被广泛接受的方案。
CHARACTER SET 数据库字符集
NATIONAL CHARACTER SET 国家字符集
CHARACTER SET 影响char,varchar,varchar2等 数据类型
NATIONAL CHARACTER SET 影响nchar,nvarchar,nvarchar2等 相对于CHARACTER SET 而言多一个"n"字符的数据类型
To specify the character set, use the CREATE DATABASE statement with the CHARACTER SET and NATIONAL CHARACTER SET clauses.
If you do not use the NATIONAL CHARACTER SET clause, then it defaults to AL16UTF16.
7.优先顺序:sql,session,initparameter,os env (high->low)
os,oracle:
$NLS_LANG=american
$NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss'
sqlplus sys/oracle as sysdba
sql>select sysdate from dual;
01-6? -09
SQL> show parameter nls_language
SIMPLIFIED CHINESE
SQL> alter system set nls_language=american scope=spfile;
alter system set NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss' scope=spfile;
alter system set NLS_TIME_FORMAT='hh24:mi:ss' scope=spfile;
SQL> shutdown immediate
SQL> startup
SQL> show parameter nls_language
AMERICAN
SQL> show parameter NLS_DATE_FORMAT
yyyy-mm-dd hh24:mi:ss
sql>select sysdate from dual;
2009-06-01 22:19:34
show parameter nls_
SELECT sysdate FROM dual;
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
(DBMS_SESSION.SET_NLS('NLS_DATE_FORMAT','''DD.MM.YYYY'''))
SELECT sysdate FROM dual;
alter system set nls_date_format='yyyy-mm-dd hh24:mi:ss' scope=spfile;
try:
win:
C:\>sqlplus scott/tiger@10g
SQL> SELECT sysdate FROM dual;
01-6月 -09
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
会话已更改。
SQL> SELECT sysdate FROM dual;
2009-06-01 22:22:30
nls:national language support
select sysdate,current_date,systimestamp from dual;
to_char(),to_date(),date类型存储精确到秒的日期和时间。
since oracle9i,extract(),timestamp类型存储精确到十亿分之一秒，默认精度为6，有效值0-9。interval year to month,interval day to second.
create table test(tscol timestamp(5));
insert into test values(systimestamp);
select * from test;
29-11月-04 10.04.20.23474 上午
create table test(tscol timestamp(5) with time zone); 时区显示为UTC的一个偏移值。
29-11月-04 10.05.02.77106 上午 +08:00
create table test(tscol timestamp(5) with local time zone); 会话时区的数据。
29-11月-04 10.05.34.68421 上午
show parameter NLS_TIMESTAMP_FORMAT
alter system set NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI:SS.FF' scope=spfile; ( ALTER SESSION SET )
shutdown immediate
startup
select to_timestamp('2008 01 09 13:20:30.90','rrrr mm dd hh24:mi:ss.ff') from dual;
select to_timestamp('2008-01-09 13:20:30.90','YYYY-MM-DD HH24:MI:SS.FF') from dual;
--select to_char('YYYY-MM-DD HH24:MI:SS.FF',to_timestamp('2008-01-09 13:20:30.90','YYYY-MM-DD HH24:MI:SS.FF')) from dual;
--to_char不能用在timestamp,interval
EXTRACT(timepart FROM interval_expression)
SELECT
experiment_id,
experiment_desc,
EXTRACT(YEAR FROM experiment_start) || ' year, ' ||
EXTRACT (MONTH FROM experiment_start) || ' month, ' ||
EXTRACT(DAY FROM experiment_start) || ' day, ' ||
EXTRACT (HOUR FROM experiment_start) || ' hour, ' ||
EXTRACT (MINUTE FROM experiment_start)|| ' minute, ' ||
EXTRACT (second FROM experiment_start)|| ' second' ,
EXTRACT(DAY FROM experiment_duration) || ' days, ' ||
EXTRACT (HOUR FROM experiment_duration) || ' hours, ' ||
EXTRACT (MINUTE FROM experiment_duration)|| ' minutes, ' ||
EXTRACT (second FROM experiment_duration)|| ' seconds' Duration
FROM experiment;
8.排序
select * from v$nls_valid_values 可用有效值
Binary sorts are the fastest type of sort.
stroke 笔画
PinYin 拼音
radical 偏旁 部首
a radical word 词根
(be) derived from 得自
Default value derived from the NLS_LANG environment variable
The default value is derived from the initialization parameter NLS_LANGUAGE.
alter session set nls_sort='schinese_pinyin_m';
select * from dept order by nlssort(name,'NLS_SORT=SCHINESE_PINYIN_M');
Oracle9i之前，中文是按照二进制编码进行排序的。
在oracle9i中新增了按照拼音、部首、笔画排序功能。设置NLS_SORT值
SCHINESE_RADICAL_M 按照部首（第一顺序）、笔划（第二顺序）排序
SCHINESE_STROKE_M 按照笔划（第一顺序）、部首（第二顺序）排序
SCHINESE_PINYIN_M 按照拼音排序，系统的默认排序方式为拼音排序
select * from v$nls_valid_values order by 1,2 可获得nls_参数有效值
select ascii('a'),'a' from dual;
举例如下：
表名为 dept ，其中name字段是中文，下面分别实现按照单位名称的笔划、部首和拼音排序。
1: //按照笔划排序
select * from dept order by nlssort(name,'NLS_SORT=SCHINESE_STROKE_M');
2: //按照部首排序
select * from dept order by nlssort(name,'NLS_SORT=SCHINESE_RADICAL_M');
3: //按照拼音排序，此为系统的默认排序方式
select * from dept order by nlssort(name,'NLS_SORT=SCHINESE_PINYIN_M'); 注意，该SQL指令并非标准指令，在SQLServer下面的实现方式并不相同。
ALTER SESSION SET NLS_SORT=BINARY;
SELECT fr_word FROM words ORDER BY fr_word;
SELECT fr_word FROM words ORDER BY NLSSORT(fr_word,'NLS_SORT=FRENCH_M');
SELECT cust_last_name
FROM customers
WHERE NLSSORT(cust_last_name, 'NLS_SORT = generic_m_ai')
= NLSSORT('De Niro', 'NLS_SORT=generic_m_ai');
ALTER SESSION SET NLS_SORT=generic_m_ai;
ALTER SESSION SET NLS_COMP=ansi;
CREATE INDEX sys_function_FUNC_NAME ON sys_function(NLSSORT(FUNC_NAME, 'NLS_SORT=SCHINESE_PINYIN_M'));
SELECT * FROM sys_function order by FUNC_NAME;
9.NLS Data Conversion with Oracle Utilities
把client和db的字符集设置成一样 NLS_LANG
exp imp
External tables
sql*Loader
expdp impdp
source client target
(1)same
(2)Conversion,子->父
Performance is optimized if all character sets are the same.
The character set of the data file can be set up by using the NLS_LANG parameter or by specifying the CHARACTERSET parameter
in the SQL*Loader control file:
LOAD DATA
CHARACTERSET UTF16
INFILE ulcase11.dat
REPLACE …
9.VIEW
SELECT PARAMETER,VALUE FROM NLS_DATABASE_PARAMETERS; //数据库语言参数表
SELECT PARAMETER,VALUE FROM NLS_INSTANCE_PARAMETERS; //实例语言参数表
SELECT PARAMETER,VALUE FROM NLS_SESSION_PARAMETERS; //会话语言参数表,跟操作系统有关
SELECT PARAMETER,VALUE FROM V$NLS_VALID_VALUES;//(LANGUAGE,SORT,TERRITORY,CHARACTERSET)
SELECT PARAMETER,VALUE FROM V$NLS_PARAMETERS;//(NLS session parameters,NLS_CHARACTERSET),有效的值列表
ALTER SESSION SET NLS_LANGUAGE='SIMPLIFIED CHINESE';//语言环境变成了中文
ALTER SESSION SET NLS_TERRITORY=CHINA;//区域改为中国
ALTER SESSION SET NLS_SORT = 'SCHINESE_PINYIN_M';//拼音排序
ALTER SESSION SET NLS_DATE_FORMAT='DD.MM.YYYY';
EXECUTE DBMS_SESSION.SET_NLS('NLS_DATE_FORMAT','''DD.MM.YYYY''');
SELECT TO_CHAR(SYSDATE,'YYYY.MM.DD','NLS_DATE_LANGUAGE='FRENCH') FROM DUAL;
CMD
SET NLS_LANG=AMERICAN_AMERICA.US7ASCII//语言:美语 区域:美国 字符编码:7位的ASCII
SQLPLUSW /NOLOG
CONN SYS/SYS AS SYSDBA //可以看到语言环境变成英语
10.汉字不能正确显示
1).原因
要在客户端正确显示ORACLE 数据库汉字信息，首先必须使服务器端的字符集与客户端的字符集一致；
其次是加载到ORACLE数据库的数据字符集必须与服务器指定字符集一致。
因此，把用户存在的问题归纳分类，产生汉字显示异常的原因大致有以下几种：
1. 1 服务器指定字符集与客户字符集不同，而与加载数据字符集一致。
这种情况是最常见的，只要把客户端的字符集设置正确即可，解决办法见2.1。
1. 2 服务器指定字符集与客户字符集相同,与加载数据字符集不一致。
这类问题一般发生在ORACLE版本升级或重新安装系统时选择了与原来服务器端不同的字符集，
而恢复加载的备份数据仍是按原字符集卸出的场合,以及加载从其它使用不同字符集的ORACLE数据库卸出的数据的情况。
这两种情况中，不管服务器端和客户端字符集是否一致都无法显示汉字。解决办法见2.2。
1.3 服务器指定字符集与客户字符集不同,与输入数据字符集不一致。
这种情况是在客户端与服务器端字符集不一致时，从客户端输入了汉字信息。输入的这些信息即便是把客户端字符集更改正确，
也无法显示汉字。解决办法见2.3。
2)．解决办法
下面将分别对上述三种情况给出解决办法。为了叙述方便，假设客户端使用WINDOWS环境，
并已成功地配置了TCP/IP协议，安装了ORACLE的sql*net，sql*pluse产品
2.1 设置客户端字符集与服务器端字符集一致
假设当前服务器端使用US7ASCII字符集。
（1）查看服务器端字符集
通过客户端或服务器端的sql*plus登录ORACLE的一个合法用户，执行下列SQL语句：
select * from V$NLS_PARAMETERS
（2）按照服务器端字符集对客户端进行配置
配置方法有两种：
安装ORACLE的客户端软件时指定
安装ORACLE的客户端产品软件时，选择与ORACLE服务端一致的字符集（本例为US7ASCII）即可。
修改注册信息的方法
根据ORACLE 客户端所选sql*net 的版本分为下列两种情况：
a. 客户端为 sql*net 2.0 以下版本
进入Windows的系统目录，编辑oracle.ini文件，用US7ASCII替换原字符集，重新启动计算机，设置生效。
b. 客户端为 sql*net 2.0 以上版本
在WIN98 下 运 行REGEDIT,第一步选HKEY_LOCAL_MACHINE,第二步选择SOFTWARE，
第三步选择 ORACLE， 第四步选择 NLS_LANG， 键 入 与服 务 器 端 相 同 的 字 符 集（本例为：AMERICAN_AMERICAN.US7ASCII）。
2.2 强制加载数据字符集与服务器端字符集一致
假设要加载数据从原ORACLE数据库卸出时的字符集为US7ASCII，当前ORACLE服务器字符集为WE8ISO8859P1。
下面提供三种解决方法：
（1） 服务器端重新安装ORACLE
在重新安装ORACLE 时选择与原卸出数据一致的字符集（本例为US7ASCII）。
加载原卸出的数据。
这种情况仅仅使用于空库和具有同一种字符集的数据。
（2）强行修改服务器端ORACLE当前字符集
在用imp命令加载数据前，先在客户端用sql*plus登录system DBA用户，执行下列SQL语句进行当前ORACLE数据库字符集修改：
SQL > create database character set US7ASCII
* create database character set US7ASCII
ERROR at line 1:
ORA-01031: insufficient privileges
你会发现语句执行过程中，出现上述错误提示信息，此时不用理会，实际上ORACLE数据库的字符集已被强行修改为US7ASCII，
接着用imp命令装载数据。等数据装载完成以后，shutdown 数据库，再startup 数据库，用合法用户登录ORACLE数据库，
在sql>;命令提示符下，运行select * from V$NLS_PARAMETERS，可以看到ORACLE数据库字符集已复原，这时再查看有汉字字符数据的表时，
汉字已能被正确显示。
（3）利用数据格式转储，避开字符集限制
这种方法主要用于加载外来ORACLE数据库的不同字符集数据。其方法如下：
先将数据加载到具有相同字符集的服务器上，然后用转换工具卸出为foxbase 格式或access格式数据库，
再用转换工具转入到不同字符集的ORACLE数据库中，这样就避免了ORACLE字符集的困扰。目前数据库格式转换的工具很多,
象power builder5.0以上版本提供的pipeline，Microsoft Access数据库提供的数据导入/导出功能等。转换方法参见有关资料说明。
11.配置客户端字符集
将本机Oracle客户端的字符集配置得和源数据库的字符集一致。
打开注册表，找到HKEY_LOCAL_MACHINE，选下面的SOFTWARE，再选Oracle，KEY_OraDb10g_home1
在注册表中找到NLS_LANG,将值改为：AMERICAN_AMERICA.ZHS16CGB231280。
配置DBCONSOLE的启动实例
打开注册表，找到HKEY_LOCAL_MACHINE，选下面的SOFTWARE，再选Oracle，SYSMAN
阅读(2594) | 评论(0) | 转发(0) |
