糾正要求修改資料庫NLS_LENGTH_SEMANTICS參數的錯誤要求，nlslengthsemantics

糾正要求修改資料庫NLS_LENGTH_SEMANTICS參數的錯誤要求，nlslengthsemantics
1、開發人員錯誤的要求

      先看一封開發人員向某DBA提出的一個“要求修改資料庫NLS_LENGTH_SEMANTICS參數並重啟資料庫”的郵件：

   

    上面郵件，出於對隱私的保護，對寄件者，收件者，資料庫名稱進行了隱塗。

郵件內容主要意思是：

    （1）   源端和目標端資料庫的字元集均為SIMPLIFIED CHINESE_CHINA.UTF8，但是源端資料庫NLS_LENGTH_SEMANTICS參數的值為char，目標資料庫NLS_LENGTH_SEMANTICS參數的值為byte

    （2）   郵件中對知識錯誤的理解：由於源端資料庫NLS_LENGTH_SEMANTICS參數的值為char（把1個漢字當成一個位元組），目標資料庫NLS_LENGTH_SEMANTICS參數的值為byte（把1個漢字佔3個位元組），所以，源端Varchar2(16)能儲存16個漢字，而目標端Varchar2(16)即只能存5個漢字，導致來源資料的資料無法插入到目標端資料庫中去

    （3）   郵件中錯誤的建議解決辦法：將目標端資料庫的NLS_LENGTH_SEMANTICS參數的值，改成與源端資料庫NLS_LENGTH_SEMANTICS參數相同的值

2、知識的梳理2.1 NLS_LENGTH_SEMANTICS參數的用途

        NLS_LENGTH_SEMANTICS參數是一個專為建立CHAR和VARCHAR2兩種字元型的列時，指定使用的位元組長度，還是使用字元長度的定義方式，有byte和char兩種值，預設為byte。

        當設定該參數為BYTE時，定義CHAR列或VARCHAR2列採用位元組長度方式；當設定該參數為CHAR時，定義CHAR列或VARCHAR2列採用字元長度的方式。該參數對於資料庫中已經存在的列不具備任何用途，只是在建立表，或修改表的列時才具有意義。

2.2 位元組長度與字元長度的區別

     此章節從百度文庫摘抄，原文地址為：http://baike.baidu.com/link?url=gtnaOI4rLZejxtdNISG3z8Vm1IpobqAB4nv3TRSnKh9RwTo2eR8eRkUWUUv00J7INVvGPQ2O51o-r77SfyIwT_

（1）ASCII碼：

      一個英文字母（不分大小寫）佔一個位元組的空間，一個中文漢字佔兩個位元組的空間。一個位元字序列，在電腦中作為一個數字單元，一般為8位位元，換算為十進位。最小值0，最大值255。如一個ASCII碼就是一個位元組。

（2）UTF-8編碼：

      一個英文字元等於一個位元組，一個中文（含繁體）等於三個位元組。

（3）   Unicode編碼：

      一個英文等於兩個位元組，一個中文（含繁體）等於兩個位元組。

（4）   符號：

      一個英文標點佔一個位元組，一個中文標點佔兩個位元組。舉例：英文句號“.”佔1個位元組的大小，中文句號“。”佔2個位元組的大小。

3、郵件中對知識錯誤的理解

        郵件中要求修改目標端資料庫NLS_LENGTH_SEMANTICS參數，是完全錯誤的解決方案，之所以出現這樣的情況，是因為此開發人員對NLS_LENGTH_SEMANTICS參數的理解不正確。

        該開發人員，錯誤的將NLS_LENGTH_SEMANTICS參數理解成，只要該參數一改，資料庫中所有的涉及CHAR和VARCHAR2兩種字元型的列的長度類型都發生變化了。

        其實不是，NLS_LENGTH_SEMANTICS參數的值，不對已經存在的列產生任何影響，只是在建立表中的列時，預設的指定列長度類型為byte還是char，如果在建立或修改表的列時指定了長度類型，完全覆蓋NLS_LENGTH_SEMANTICS參數的值。

4、剖析問題的真正原因

        其實，該開發人員所面對的真正問題原因，是源端表欄位的長度類型與目標端表欄位長度的類型不一致所致。

        問題根本原因搞清楚了，解決方案就容易了，將目標端表的欄位長度類型修改成與源端一樣，不就解決了木。何必修改資料庫參數還重啟資料庫的。

        下面以三條create table的語句說清楚NLS_LENGTH_SEMANTICS參數的用途

（1）兩條指定長度類型的SQL語句 create table tab_t(t_name varchar2(20byte)); create table tab_t(t_name varchar2(20char));         上面兩條語句，唯一的不同，就是在指定列長度為20後，再指定長度的類型，類型的值不同。 （2）不指定長度類型的SQL語句 create table tab_t(t_name varchar2(20));         這條語句，在指定列的長度為20後，並未指定長度的類型，那它的類型會是什麼呢，這個就是由NLS_LENGTH_SEMANTICS參數的值所決定了，該參數值可以在會話級設定。

5、測實驗證5.1 確認資料庫的字元集類型

SQL> select *from nls_database_parameters t where t.parameter='NLS_CHARACTERSET';

PARAMETER                      VALUE ------------------------------   -------------------------------------- NLS_CHARACTERSET               AL32UTF8

 

5.2 建立列長度類型為byte的表並測試可插入資料長度

（1）查看NLS_LENGTH_SEMANTICS參數當前值

SQL> selectname,value from v$parameter where upper(name)='NLS_LENGTH_SEMANTICS';

NAME                 VALUE ------------------------------   ------------------------------- nls_length_semantics     BYTE

 

（2）建立帶列長度類型為byte的表

SQL>create table tab_t(t_name varchar2(3));

（3）查看新建立的tab_t表的t_name列長度類型

SQL>select table_name,column_name,data_type,char_usedfrom dba_tab_columnswhere table_name='TAB_T'

TABLE_NAME    COLUMN_NAME      DATA_TYPE     CHAR_USED                                                                     --------------------  -----------------------    -------------------------  ----------------------------- TAB_T                   T_NAME                    VARCHAR2        B

（4）插入英文字串資料測試

$ export NLS_LANG=AMERICAN_AMERICA.UTF8 --注意上面這一條，設定用戶端字元集很重要，如果環境變數有設定，此步可以跳過。如果發生複雜的字元集轉換，一個中文漢字有可能會佔用6個位元組   SQL> insert into tab_t values ('ZHO');  1 row created.   SQL> insert into tab_t values ('ZHON'); insert into tab_t values ('ZHON')                           * ERROR at line 1: ORA-12899: value too large for column "SYS"."TAB_T"."T_NAME" (actual: 4,maximum: 3)

從上面測試資料來看，插入三個英文字母成功，在插入四個字母的字串時失敗，提示實際長度為4，但maximum只有3

（5）   插入中文字串資料測試

1）先計劃一下“中”字佔用幾個位元組  SQL> SELECT  LENGTHB('中') FROM DUAL; LENGTHB('中') -------------             3 2）插入一個中文漢字 SQL> insert into tab_t values ('中'); 1 row created.   3）插入兩個中文漢字 SQL> insert into tab_t values ('中國'); insert into tab_t values ('中國')                           * ERROR at line 1: ORA-12899: value too large for column "SYS"."TAB_T"."T_NAME" (actual: 6, maximum: 3)

插入兩個中文漢字失敗，實際長度為6，欄位maximum只有3，在此驗證確定，在UTF8下，一個中文漢字佔3個字元。

 

5.3 將tab_t表的t_name列更改成char長度類型並做可插入長度測試

（1）將tab_t表的t_name列長度類型更改成char

SQL>alter table tab_t modify (t_name varchar2(3char));

（2）驗證修改結果

SQL> selecttable_name,column_name,data_type,char_used from dba_tab_columns wheretable_name='TAB_T' ;

TABLE_NAME    COLUMN_NAME      DATA_TYPE     CHAR_USED                                                                     --------------------  -----------------------    -------------------------  ----------------------------- TAB_T                    T_NAME                   VARCHAR2        C

（3）   插入兩個中文漢字

SQL> insert into tab_t values ('中國');   1 row created.

 varchar2(3 char)插入兩個中文漢字成功

 

6、小結

        經過對開發人員的需求進行判斷，以及糾正其對NLS_LENGTH_SEMANTICS參數用途錯誤的理解，用修改表欄位長度類型的方式解決其面臨的實際問題，避免了一次不必要的資料庫重啟，以及問題得到真正的解決。

 

 

本文作者：黎俊傑（網名：踩點），從事”系統架構、作業系統、存放裝置、資料庫、中介軟體、應用程式“六個層面系統性的效能最佳化工作

歡迎加入系統效能最佳化專業群，共同探討效能最佳化技術。群號：258187244