NLS_LENGTH_SEMANTICS参数引申的问题

本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer,已开源在github ,欢迎star!

转载声明:转载请注明出处,本技术博客是本人原创文章

本文GitHub https://github.com/OUYANGSIHAI/JavaInterview 已收录,这是我花了6个月总结的一线大厂Java面试总结,本人已拿大厂offer,欢迎star

原文链接:blog.ouyangsihai.cn >> NLS_LENGTH_SEMANTICS参数引申的问题

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

问题:

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

分析过程:

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

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

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

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

  1. 进一步看,以CHAR计算的环境中,还有一个字段CHAR_LENGTH,其值是30,从解释看,该字段含义是以字符计算的列长度,且仅适用于以下四种类型:CHAR、VARCHAR2、NCHAR、NVARCHAR。NLS_LENGTH_SEMANTICS参数引申的问题
  1. 那么现在看这个问题基本能确定了,有一套环境,DATA_LENGTH是120,CHAR_LENGTH是30,字符字段的长度是以CHAR计算的,该环境使用的是UTF-8字符集,DATA_LENGTH字段的含义是以BYTE计算,即估算按照4*30=120个字节为该列允许的字段长度。

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

解决过程:

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

NLS_LENGTH_SEMANTICS参数引申的问题

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

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

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

方法2:session级修改

ALTER SESSION SET NLS_LENGTH_SEMANTICS=’BYTE’;

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

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

  • CREATE TABLE XXX  
  • (  
  • A     VARCHAR2(1 char)  
  • );  
  • 或 view plain

  • CREATE TABLE XXX  
  • (  
  • A      VARCHAR2(1 byte)  
  • );  
  • 引申问题:

    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个中文字符,相当于存储内容扩大了。

    1. 如果使用ALTER SESSION改变的参数值,例如原来是CHAR,现在改为BYTE,再创建表CREATE TABLE,会发现其创建语句默认变为:
  • create table XXX  
  • (  
  •   A           VARCHAR2(3 BYTE)  
  • ...  
  • );  
  • 默认带有一个BYTE关键字。

    1. NLS_LENGTH_SEMANTICS默认是CHAR,为何我碰到的问题中,该值变为BYTE了?从alert日志中可以发现,启动数据库时,未采用默认参数值的部分发现了这个参数:NLS_LENGTH_SEMANTICS参数引申的问题

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

    1. 顺着(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_parametersnls_instance_parametersnls_database_parameters。nls_instance_parameters取自init.ora配置文件中的值,nls_session_parameters默认选择nls_instance_parameters的值,但如果使用ALTER SESSION或环境变量改变该值,则会选择此值。

    如果您觉得此篇文章对您有帮助,欢迎关注微信公众号:bisal的个人杂货铺,您的支持是对我最大的鼓励!

    NLS_LENGTH_SEMANTICS参数引申的问题
    本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer,已开源在github ,欢迎star!

    转载声明:转载请注明出处,本技术博客是本人原创文章

    本文GitHub https://github.com/OUYANGSIHAI/JavaInterview 已收录,这是我花了6个月总结的一线大厂Java面试总结,本人已拿大厂offer,欢迎star

    原文链接:blog.ouyangsihai.cn >> NLS_LENGTH_SEMANTICS参数引申的问题


     上一篇
    一个执行计划异常变更的案例 – 外传之绑定变量窥探 一个执行计划异常变更的案例 – 外传之绑定变量窥探
    上一篇文章《一个执行计划异常变更的案例 - 前传》,介绍了一次执行计划异常变更的案例现象,这两天经过运行同事,以及罗大师的介绍,基本了解了其中的原因和处理方法,这个案例其实比较典型,涉及的知识点很多,有数据库新特性,有SQL相关的,还有应用
    下一篇 
    一个执行计划异常变更的案例 – 外传之查看绑定变量值的几种方法 一个执行计划异常变更的案例 – 外传之查看绑定变量值的几种方法
    这篇外传之前有这么几篇文章:《一个执行计划异常变更的案例 - 前传》《一个执行计划异常变更的案例 - 外传之绑定变量窥探》 上一篇文章介绍了绑定变量以及11g之前绑定变量窥探的影响,这篇文章会介绍几种查看绑定变量值的方法。 上篇文章我们说了