NLS_LENGTH_SEMANTICS參數引申的問題，nlslengthsemantics

NLS_LENGTH_SEMANTICS參數引申的問題，nlslengthsemantics

由於某項目的特殊性，開發資料庫環境有兩套，兩邊都可能對錶結構進行一些修改，因此寫了一個工具，比對兩邊的結構中繼資料，其中碰到一個問題，很細微，但確實值得注意，在此記錄下。

問題：

比對兩個環境中同一張表的同一個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：建立表時指定計算方法

CREATE TABLE XXX(A     VARCHAR2(1 char));

或

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個中文字元，相當於儲存內容擴大了。

2. 如果使用ALTER SESSION改變的參數值，例如原來是CHAR，現在改為BYTE，再建立表CREATE TABLE，會發現其建立語句預設變為：

create table XXX(  A           VARCHAR2(3 BYTE)...);

預設帶有一個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或環境變數改變該值，則會選擇此值。

著作權聲明：本文為博主原創文章，未經博主允許不得轉載。