NLS_LENGTH_SEMANTICS參數引申的問題,nlslengthsemantics

來源:互聯網
上載者:User

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或環境變數改變該值,則會選擇此值。

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

相關文章

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.