ORA-12899: value too large for column

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。