更改Oracle实例的字符集

时间：2022-02-24 21:08:55

(1).数据库服务器字符集
select * from nls_database_parameters

来源于props$，是表示数据库的字符集。

(2).服务端字符集环境
select * from nls_instance_parameters

其来源于v$parameter，表示服务端的字符集的设置，可能是参数文件，环境变量或者是注册表

(3).会话字符集环境
select * from nls_session_parameters

来源于v$nls_parameters，表示会话自己的设置，可能是会话的环境变量或者是alter session完成，如果会话没有特殊的设置，将与nls_instance_parameters一致。

(4).客户端的字符集要求与服务器一致，才能正确显示数据库的非Ascii字符。

如果多个设置存在的时候，NLS作用优先级别：Sql function > alter session > 环境变量或注册表 > 参数文件 > 数据库默认参数

字符集要求一致，但是语言设置却可以不同，语言设置建议用英文。如字符集是zhs16gbk，则nls_lang可以是American_America.zhs16gbk。

客户端字符集设置方法
1)UNIX环境
         $NLS_LANG=“simplified chinese”_china.zhs16gbk
         $export NLS_LANG
         编辑oracle用户的profile文件
2)Windows环境
编辑注册表
Regedit.exe ---》 HKEY_LOCAL_MACHINE ---》SOFTWARE ---》 ORACLE-HOME
或者在窗口设置：
set nls_lang=AMERICAN_AMERICA.ZHS16GBK
http://blog.itpub.net/8475224/viewspace-692675/

NLS_LANG格式：
NLS_LANG = language_territory.charset
有三个组成部分（语言、地域和字符集），每个成分控制了NLS子集的特性。其中：
language 指定服务器消息的语言。
territory 指定服务器的日期和数字格式。
charset 指定字符集
从NLS_LANG的组成我们可以看出，真正影响数据库字符集的其实是第三部分。
所以两个数据库之间的字符集只要第三部分一样就可以相互导入导出数据，前面影响的只是提示信息是中文还是英文。

字符集
实质就是按照一定的字符编码方案，对一组特定的符号，分别赋予不同数值编码的集合。Oracle数据库最早支持的编码方案是US7ASCII。
Oracle的字符集命名遵循以下命名规则:
即: <语言><比特位数><编码>
比如: ZHS16GBK表示采用GBK编码格式、16位（两个字节）简体中文字符集
http://blog.itpub.net/8475224/viewspace-692675/

例如：
AMERICAN_AMERICA.US7SCII
AMERICAN _ AMERICA. ZHS16GBK
只显示了语言信息，是因为数据库安装时默认使用OS的地域和字符集。
安装Oracle的时候不是有个显示字符集选择的步骤吗，那个时候安装程序自己就带出了一个OS的字符集作为默认选择，当然你可以更改的。
select * from nls_session_parameters;
更改Oracle实例的字符集（服务器端）：

--SIMPLIFIED CHINESE_CHINA.ZHS16GBK

--=<Language>_<Territory>.<Clients Characterset>

select t.name,t.value$,t.rowid from sys.props$  t where name='NLS_LANGUAGE' or name='NLS_CHARACTERSET' or name like 'NLS_TERRITORY';

具体更改情况参考以下update语句：

update sys.props$ set value$='SIMPLIFIED CHINESE'  where name='NLS_LANGUAGE';

commit;

update sys.props$ set value$='ZHS16GBK' where name='NLS_CHARACTERSET' ;

commit;

update sys.props$ set value$='CHINA'  where name='NLS_TERRITORY';

commit;

如果更改后没有立即生效，重启此实例。

查看服务器端字符集：
select * from v$nls_parameters;
select * from nls_database_parameters;
select * from sys.props$;

http://www.linuxidc.com/Linux/2010-03/24767.htm

在做ETL的时候，经常有一些字段提示长度不够。今天偶抓住一张表来分析了一下，后来发现中文在不同字符集所占用的字节是不同的。详细如下：

源数据库字符集为: ZHS16GBK，数据仓库的字符集为：UTF-8

查询字符集：
SELECT * FROM DATABASE_PROPERTIES WHERE property_name = 'NLS_CHARACTERSET'

源数据库：（ZHS16GBK）

SQL> SELECT '中文', length('中文') x, lengthb('中文') xb FROM dual;
'中文'          X         XB
------ ---------- ----------
中文            2          4

目标数据库：（UTF8）

SQL> SELECT '中文', length('中文') x, lengthb('中文') xb FROM dual;
'中文'                X         XB 更改Oracle实例的字符集
------------ ---------- ---------- 更改Oracle实例的字符集
中文                  2          6

做了一个表测试了一下：

在源数据库里：

SQL> create table cntable(fname varchar2(4)); 更改Oracle实例的字符集更改Oracle实例的字符集
Table created 更改Oracle实例的字符集
SQL> insert into cntable(fname) values('张三');
1 row inserted 更改Oracle实例的字符集

在目标数据仓库里：

SQL> create table cntable(fname varchar2(4)); 更改Oracle实例的字符集更改Oracle实例的字符集
Table created 更改Oracle实例的字符集更改Oracle实例的字符集
SQL> insert into cntable(fname) values('张三'); 更改Oracle实例的字符集
insert into cntable(fname) values('张三') 更改Oracle实例的字符集
ORA-12899: value too large for column "HUB"."CNTABLE"."FNAME" (actual: 6, maximum: 4) 更改Oracle实例的字符集

很显然中文在UTF8里占3个字节，ZHK里占2个字节。

备注：

Purpose

The LENGTH functionsreturn the length of char. LENGTH calculates length usingcharacters as defined by the input character set.

--返回以字符为单位的长度.

LENGTHB usesbytes instead of characters.

--返回以字节为单位的长度.

LENGTHC usesUnicode complete characters.

--返回以Unicode完全字符为单位的长度.

LENGTH2 usesUCS2 code points.

--返回以UCS2代码点为单位的长度.

LENGTH4 usesUCS4 code points.

--返回以UCS4代码点为单位的长度.

在不同的数据库，因为字符集的不同，LENGTHB得到的值可能会不一样。如ZHS16GBK采用两个byte位来定义一个汉字。而在UTF8，采用3个byte。

SYS@anqing1(rac1)> SELECT USERENV('LANGUAGE') FROM DUAL;

USERENV('LANGUAGE')

----------------------------------------------------

AMERICAN_AMERICA.ZHS16GBK

SQL>select length('安庆') from dual;

2

SQL>select lengthb('安庆') from dual;

4

SQL>select length('AnQing') from dual;

6

SQL>select lengthb('AnQing') from dual;

6

通过这个示例，我们可以看出来，Length 和 Lengthb 函数的一个重要用处，就是用来判断记录值里是否有中文内容。

如果有中文，那么Length() != Lengthb()

如果没有中文，那么Length() == Lengthb()

http://blog.csdn.net/tianlesoftware/article/details/6863797

USEREVN -- 官方文档 functions228.htm#i79862
Oracle建议用 sys_context 来代替 userenv

USERENV('DB_NAME')  《--------》SYS_CONTEXT('USERENV','DB_NAME');

返回当前用户环境<也就是sqlplus 所在电脑的环境>的信息,opt可以是:
ENTRYID,SESSIONID,TERMINAL,ISDBA,LABLE,LANGUAGE,CLIENT_INFO,LANG,VSIZE

USEREVN()

1.ISDBA 查看当前用户是否是DBA如果是则返回true

SQL> select userenv('isdba') from dual;

USEREN
------
FALSE

2.SESSION 返回会话标志

SQL> select userenv('sessionid') from dual;

USERENV('SESSIONID')
--------------------
152

4.ENTRYID 返回会话人口标志

SQL> select userenv('entryid') from dual;

USERENV('ENTRYID')
------------------
0

5.INSTANCE 返回当前INSTANCE的标志
SQL> select userenv('instance') from dual;

USERENV('INSTANCE')
-------------------
1

6.LANGUAGE 返回当前环境变量
SQL> select userenv('language') from dual;

USERENV('LANGUAGE')
----------------------------------------------------
SIMPLIFIED CHINESE_CHINA.ZHS16GBK

7.LANG 返回当前环境的语言的缩写
SQL> select userenv('lang') from dual;

USERENV('LANG')
----------------------------------------------------
ZHS

8.TERMINAL 返回用户的终端或机器的标志
SQL> select userenv('terminal') from dual;

USERENV('TERMINA
----------------
GAO

9.VSIZE(X) 返回X的大小(字节)数
SQL> select vsize(user),user from dual;

VSIZE(USER) USER
----------- ------------------------------
6 SYSTEM

select 　　SYS_CONTEXT('USERENV', 'DB_NAME') db_name,

       　　SYS_CONTEXT('USERENV', 'CURRENT_USER') current_user,

       　　SYS_CONTEXT('USERENV', 'LANGUAGE') language,

       　　SYS_CONTEXT('USERENV', 'OS_USER') os_user,

       　　SYS_CONTEXT('USERENV', 'TERMINAL') terminal,

       　　SYS_CONTEXT('USERENV', 'SESSIONID') sessionid,

       　　SYS_CONTEXT('USERENV', 'INSTANCE') instance,

       　　SYS_CONTEXT('USERENV', 'ENTRYID') entryid,

       　　SYS_CONTEXT('USERENV', 'ISDBA') isdba,

       　　SYS_CONTEXT('USERENV', 'NLS_TERRITORY') nls_territory,

       　　SYS_CONTEXT('USERENV', 'NLS_CURRENCY') nls_currency,

       　　SYS_CONTEXT('USERENV', 'NLS_CALENDAR') nls_calendar,

       　　SYS_CONTEXT('USERENV', 'NLS_DATE_FORMAT') nls_date_format,

       　　SYS_CONTEXT('USERENV', 'NLS_DATE_LANGUAGE') nls_date_language,

       　　SYS_CONTEXT('USERENV', 'NLS_SORT') nls_sort,

       　　SYS_CONTEXT('USERENV', 'CURRENT_USERID') current_userid,

       　　SYS_CONTEXT('USERENV', 'SESSION_USER') session_user,

       　　SYS_CONTEXT('USERENV', 'SESSION_USERID') session_userid,

       　　SYS_CONTEXT('USERENV', 'PROXY_USER') proxy_user,

       　　SYS_CONTEXT('USERENV', 'PROXY_USERID') proxy_userid,

       　　SYS_CONTEXT('USERENV', 'DB_DOMAIN') db_domain,

       　　SYS_CONTEXT('USERENV', 'HOST') host,

       　　SYS_CONTEXT('USERENV', 'EXTERNAL_NAME') external_name,

       　　SYS_CONTEXT('USERENV', 'IP_ADDRESS') ip_address,

       　　SYS_CONTEXT('USERENV', 'NETWORK_PROTOCOL') network_protocol,

       　　SYS_CONTEXT('USERENV', 'BG_JOB_ID') bg_job_id,

       　　SYS_CONTEXT('USERENV', 'FG_JOB_ID') fg_job_id,

       　　SYS_CONTEXT('USERENV', 'AUTHENTICATION_TYPE') authentication_type,

       　　SYS_CONTEXT('USERENV', 'AUTHENTICATION_DATA') authentication_data 　　from dual;

相关文章