ITPub博客

首页 > 数据库 > Oracle > NLS_LENGTH_SEMANTICS

NLS_LENGTH_SEMANTICS

原创 Oracle 作者:bisal 时间:2015-09-14 14:12:20 0 删除 编辑

由于某项目的特殊性,开发数据库环境有两套,两边都可能对表结构进行一些修改,因此写了一个工具,比对两边的结构元数据,其中碰到一个问题,很细微,但确实值得注意,在此记录下。


问题:

比对两个环境中同一张表的同一个VARCHAR2类型的字段长度时,发现一个环境中其长度是30,一个环境中其长度是120,两个环境中建表语句该字段定义都是VARCHAR2(30)。

比较字段长度使用的是user_tab_cols视图中DATA_LENGTH这个字段。


分析过程:

1. 查看文档中对DATA_LENGTH的定义,该值是NUMBER类型的,描述是“列的长度(以字节)”



2. 再比对两边环境中user_tab_cols视图中的字段,有个字段引起注意,CHAR_USED,一边是C,一边是B。

再查看这个字段的解释,CHAR_USED是一位的字符类型,B代表该列使用BYTE计算其长度,C表示该列使用CHAR计算其长度,NULL空值表示该列类型不是以下四种之一:CHAR、VARCHAR2、NCHAR、NVARCHAR2。

从以上示例的结果看,说明两边库计算字段长度的单位不同,一边是以BYTE计算,一边是以CHAR计算。



3. 进一步看,以CHAR计算的环境中,还有一个字段CHAR_LENGTH,其值是30,从解释看,该字段含义是以字符计算的列长度,且仅适用于以下四种类型:CHAR、VARCHAR2、NCHAR、NVARCHAR。



4. 那么现在看这个问题基本能确定了,有一套环境,DATA_LENGTH是120,CHAR_LENGTH是30,字符字段的长度是以CHAR计算的,该环境使用的是UTF-8字符集,DATA_LENGTH字段的含义是以BYTE计算,即估算按照4*30=120个字节为该列允许的字段长度。

如果字符类型字段是以CHAR定义,那么DATA_LENGTH字段的值会取决于数据库字符集,表示的是字节。


解决过程:

字符类型长度的计算是受一个NLS_LENGTH_SEMANTICS参数的控制,其默认值是BYTE,该参数是可以动态修改的,一般情况下都是用字节计算,但有时可能为了特殊的应用要求,改为以字符计算。




方法1:系统级修改,永久生效

ALTER SYSTEM SET NLS_LENGTH_SEMANTICS='BYTE', SCOPE=BOTH;

不需要重启数据库,立即生效。


方法2:session级修改

ALTER SESSION SET NLS_LENGTH_SEMANTICS='BYTE';

当前SESSION生效,但只会影响接下来的CREATE TABLE语句,已有表则不会受影响。


方法3:创建表时指定计算方法

  1. CREATE TABLE XXX  
  2. (  
  3. A     VARCHAR2(1 char)  
  4. );  

  1. CREATE TABLE XXX  
  2. (  
  3. A      VARCHAR2(1 byte)  
  4. );  


引申问题:

1. 如果原来NLS_LENGTH_SEMANTICS值是CHAR,现在改为BYTE,则可能需要注意的是,创建新表字段时,需要考虑应用对这些字符的处理,例如应用会处理中文字符,GBK字符集下一个中文字符占用2个字节,UTF-8下一个中文字符占用3个字符,CHAR改为BYTE方式存储,定义为VARCHAR2(10)类型的字段,原来CHAR时,可以存储10个字符,如果都是中文字符,GBK字符集,那么实际字节是20,现在只能存储10个字节,即5个中文字符。UTF-8字符集,那么实际字节是30,现在只能存储3个中文字符。相当于存储内容缩小了。

如果是从BYTE改为CHAR,那么 VARCHAR2(10),原先只能存储10个字节,GBK字符集下,只能存储5个中文字符,现在改为CHAR计算存储,那么实际能存储10个字符。UTF-8字符集下,原先能存储3个中文字符,现在能存储10个中文字符,相当于存储内容扩大了。

2. 如果使用ALTER SESSION改变的参数值,例如原来是CHAR,现在改为BYTE,再创建表CREATE TABLE,会发现其创建语句默认变为:

  1. create table XXX  
  2. (  
  3.   A           VARCHAR2(3 BYTE)  
  4. ...  
  5. );  
默认带有一个BYTE关键字。

3. NLS_LENGTH_SEMANTICS默认是CHAR,为何我碰到的问题中,该值变为BYTE了?

从alert日志中可以发现,启动数据库时,未采用默认参数值的部分发现了这个参数:


再查对应的init.ora配置文件,发现确实对该值设置了char,因此CREATE TABLE默认字符类型长度都是以CHAR计算。

4. 顺着(3),对于数据库参数设置,有三个视图:nls_database_parameters、nls_instance_parameters和nls_session_parameters,都有该参数的设置,对应值为:

nls_database_parameters中NLS_LENGTH_SEMANTICS是BYTE

nls_instance_parameters中NLS_LENGTH_SEMANTICS是char

nls_session_parameters中NLS_LENGTH_SEMANTICS是CHAR

nls_database_parameters取值与创建数据库时存储在数据库中的信息相关,这与环境变量和参数文件等是统统没有关系的。不会受到环境变量的影响。

nls_instance_parameters只受参数文件的影响,而不受环境变量影响。

nls_session_parameters取值默认会从nls_instance_parameters继承,但是如果,我们在环境变量或者通过ALTER SESSION 改变了nls的相关参数,则会覆盖默认值。

即,他们的使用优先级是nls_session_parameters>nls_instance_parameters>nls_database_parameters。

nls_instance_parameters取自init.ora配置文件中的值,nls_session_parameters默认选择nls_instance_parameters的值,但如果使用ALTER SESSION或环境变量改变该值,则会选择此值。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7192724/viewspace-1799044/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
Oracle ACE,10g/11g OCP,11g OCM,国内首批Oracle YEP成员(Oracle Young Expert Program,Oracle用户组年轻专家项目),EXIN DevOps Master,Oracle爱好者,微信公众号:bisal的个人杂货铺

注册时间:2013-07-26

  • 博文量
    340
  • 访问量
    2645046