ORA-12899: value too large for column

昨天，同事導資料，本來一直好用的檔案，就一直在報錯：ORA-12899: value too large for column，本來以為程式修改導致欄位長度變了，就改了一張表的結構。結果可好，導下一張表依然報錯。。報錯也好，也就確定了不是程式問題，是DB做的有問題。後來靠著強大的google找到了答案，才覺得Oracle error code也很強大！

 

通常會查詢NLS_CHARACTERSET（資料庫字元集），NLS_NCHAR_CHARACTERSET（國家字元集），應該要儲存多種語言，需要字元集為UTF-8。

SELECT * FROM V$NLS_PARAMETERS WHERE PARAMETER IN ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET');

結果UTF-8，OK。

查看沒問題的DB裡 FieldA varchar2(10 char)

查看有問題的DB裡 FieldA varchar2(10)

（沒出現問題之前還真沒注意到這兩種定義是有區別的。。。）

UTF-8裡一個中文字元是3 bytes，從上面的定義可以看出來，如果char/byte 定義導致的可儲存資料長度相差很大了。

 

設定參數NLS_LENGTH_SEMANTICS可以在create table時對CHAR 或者VARCHAR2列指定使用位元組（byte）或者字元（character）來定義長度。

NCHAR, NVARCHAR2, CLOB, and NCLOB 列都是基於字元（character）的。

NLS_LENGTH_SEMANTICS不會影響到SYS和SYSTEM使用者表，資料字典定義都使用位元組（byte）。

 

可以在定義列時候顯示指定使用位元組（byte）或者字元（character）來定義長度：

CHAR(10 BYTE)  - 無論NLS_LENGTH_SEMANTICS設定成什麼，都使用位元組（byte）。

CHAR(10 CHAR) - 無論NLS_LENGTH_SEMANTICS設定成什麼，都使用字元（char）。

 

select * from v$nls_parameters where parameter = 'NLS_LENGTH_SEMANTICS';

PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
NLS_LENGTH_SEMANTICS
BYTE

ALTER SYSTEM SETNLS_LENGTH_SEMANTICS=CHAR scope=BOTH;
修改完畢重啟DB，show parameter NLS_LENGTH_SEMANTICS 依然是BYTE。

Update  props$ set VALUE$=‘CHAT’ Where name=‘NLS_LENGTH_SEMANTICS’;

--好吧，不知道直接update有沒影響，不是product DB才可以這麼囂張的操作的。。貌似很多人不讓這樣改字元集，其實改完參數就應該繼續往下測試，避免用這樣極端的修改方式。

NLS_LENGTH_SEMANTICS值更改為了CHAR

select * from v$nls_parameters where parameter = 'NLS_LENGTH_SEMANTICS';

PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
NLS_LENGTH_SEMANTICS
CHAR

再導資料依然報錯。。。

重建剛才的表，就發現之前定義varchar2(10) 變成了varchar2(10 char)，也就是說修改生效了。但是為什麼還是不能正常導資料呢？

後來發現，NLS_LENGTH_SEMANTICS 還有一句說明：現有列不受修改影響！！

也就是說想要通過這種方法解決問題貌似行不通了，因為不想把那幾萬張使用者表都alter一遍。

 

在Create DB時候確實需要非常細緻，避免類似這種錯誤，create之後早期也要做好必要的check避免之後發生類似問題。之前還犯過把DB字元集搞錯了，結果這些參數改來改去最後還是不得不重建DB。