OGG: NLS_LENGTH_SEMANTICS报错信息ora-12899 value too large for column

1         故障记录

报告人/单位		甲骨灰			报告日期		04月
故障发生时间			故障解决时间			故障周期
故障现象描述：    某系统容灾对端ogg复制软件复制进程经常Abended。 报错信息ora-12899 value too large for column 。

2         故障分析与处理

2.1  故障产生环境记录

故障类别	硬件	硬件[UNIX主机  磁盘阵列  磁带库  网络设备]
	软件	软件[ 操作系统  数据库  其它（请注明） ]
设备序列号/软件版本		Oracle 10.2.0.4
其它

2.2  系统应急处理措施

保证业务正常运行已采取的应急处理措施（请注明处理的时间及手段）

1、修改无法插入字符字段的单位从byte到char. alter table tab_name modify col_name varchar2(char_length char);

2.3  日志分析、故障原因定位和处理措施

 

知识背景： Oracle的这个参数nls_length_semantics有byte和char两种取值，数据库可以使用这两种单位创建char或varchar2类型的数据库表列。二者的区别是一个按照字符存放，一个按字节存放。 对使用UTF-8字符集的数据库,一个汉字如果按照字节(byte)存放，会占用3个字节(byte),按字符(char)存放一个汉字只占用一个字符(char)。   NLS_LENGTH_SEMANTICS Property Description Parameter type String Syntax NLS_LENGTH_SEMANTICS = string Example: NLS_LENGTH_SEMANTICS = 'CHAR' Default value BYTE Modifiable ALTER SESSION Range of values BYTE | CHAR NLS_LENGTH_SEMANTICS enables you to create CHAR and VARCHAR2 columns using either byte or character length semantics. Existing columns are not affected. NCHAR, NVARCHAR2, CLOB, and NCLOB columns are always character-based. You may be required to use byte semantics in order to maintain compatibility with existing applications. NLS_LENGTH_SEMANTICS does not apply to tables in SYS and SYSTEM. The data dictionary always uses byte semantics. 译： NLS_LENGTH_SEMANTICS使您能够创建使用字节或字符长度语义的CHAR和VARCHAR2列。现有列不受影响。 NCHAR，NVARCHAR2，CLOB，NCLOB列总是基于字符。您可能需要使用字节语义，以保持与现有应用程序的兼容性。 NLS_LENGTH_SEMANTICS不适SYS和SYSTEM表。数据字典总是使用字节语义。     1、检查数据库的nls_length_semantics参数值是byte还是char，     select value from v$nls_parameters where parameter=’NLS_LENGTH_SEMANTICS’；         注意从：sys.nls_database_parameters里面查到的结果并不是真正的值，使用以上语句查。       说明：某系统对端在建库之后应该调整为Char，而实际上未调整，还是默认的byte类型。 这样，如果要存储的字符串全部是英文字母或数字，那么两者还看不出区别，如果字符串中有汉字，例如“中国”，在定义字段长度的时候，我们定义的是varchar(2),这个NLS_LENGTH_SEMANTICS参数如果为char，那么可以装得下，如果是byte，就装不下了。     当NLS_LENGTH_SEMANTICS取值为BYTE时，默认为BYTE，当取值为CHAR时，默认为CHAR。意思就是说，如果NLS_LENGTH_SEMANTICS=BYTE ,char(10)实际上就是 char(10 byte);如果NLS_LENGTH_SEMANTICS=CHAR,char(10)实际上就是 char(10 CHAR)。不管NLS_LENGTH_SEMANTICS取值为何，都可以在使用时显式的指定，是按CHAR还是BYTE。 例如，实例NLS_LENGTH_SEMANTICS=BYTE，在创建表时可以指定char(10 char)就可以使用char语义了。   分析： 这个值修改后只对新生成的数据生效，对原有的数据无影响，因此需要想办法把它调整过来： 修改数据库的nls_length_semanitcs参数:该参数有三个级别,分别是database,instance,session。可以分别在nls_database_parameters,nls_instance_parameters,nls_session_parameters里查询到。 数据库级的值在创建数据库时被指定，实例级的值可以通过修改数据库参数pfile或spfile来指定，会话级的可以使用alter session来指定。 实例和会话级的参数只对修改之后的对象(包括字段和plsql变量)产生作用，修改之前的维持不变。具体命令如下: alter system set nls_length_semantics=char scope=both; 注意：此参数必须重启生效，即使可以使用scope=both来修改。 3、由于修改nls_length_segmantics参数后只对修改之后的对象(包括字段和plsql变量)产生作用,修改之前的维持不变，所以还需要对已经存在的对象进行修改。     A、对于字段修改语句如下: alter table tab_name modify col_name varchar2(char_length char); 也可使用下面语句批处理执行（但需要注意在系统空闲的时候使用，对于系统复制需要停止目标端的复制进程）： select 'alter table '||owner||'.'||table_name||' modify '||column_name||' VARCHAR2('||char_length||' char);'  from dba_tab_columns s where owner IN ('AMBER','KBSD','OWF_MGR','SGPM','SGPM_API','SGPM_OUT','WF_AMBER')   and char_used='B'   and exists(select 1 from dba_objects t where t.OWNER=s.OWNER and t.OBJECT_NAME=s.TABLE_NAME              and t.OBJECT_TYPE='TABLE'); B、对于plsql变量（procedure,package,type等），直接重新编译就可以了，批处理语句如下: select 'alter '||type||' '||name||' compile;' from dba_plsql_object_settings  s where s.OWNER in ('AMBER','KBSD','OWF_MGR','SGPM','SGPM_API','SGPM_OUT','WF_AMBER')   and nls_length_semantics='BYTE' ;    注意：修改表定义后需要重现编译所有失效对象！ 查看所有失效对象并重新编译 SELECT 'ALTER '||        (case OBJECT_TYPE when 'PACKAGE BODY' then 'PACKAGE' else OBJECT_TYPE end) ||        ' '||o.owner||'.'||        o.object_name||        (case OBJECT_TYPE when 'PACKAGE BODY' then ' COMPILE BODY;' else ' COMPILE;' end) FROM DBA_OBJECTS  o WHERE STATUS <> 'VALID'  and o.object_type<>'SYNONYM' order by 1;

3         故障责任定位

4         改进建议或其它说明

 

建议或相关说明（如是否需要软件升级或打补丁进行改进等等。）

本次故障建议： 1、在11G以下的数据库中，不要在创建数据库时指定nls_length_semantics=CHAR(默认安装是byte),应该在创建实例后修改nls_length_semantics参数，再导入应用的数据。这是因为部分xdb和sys用户的对象不支持 CHAR语义，会产生错误，不过11.1.0.6之后已经得到解决。详见Bug 5545716 和 Bug 4886376。 2、9i以下的客户端不能识别CHAR语义，当你用8i客户端去连接9i UTF8数据库时，数据库的VARCHAR2(10 CHAR)会以BYTE语义显示为VARCHAR2(30) ,这样8i的的客户端才能插入不超过30byte的数据，鉴于此，当使用CHAR语义时，最好使用9i以上的客户端。 3、存储过程或包在创建或者重新编译的时候会读取当前SESSION的NLS_LENGTH_SEMANTICS值，并存入代码中。考虑下面的问题当前会话使用的是BYTE语义，你创建了一个存储过程，里面有一个变量的类型声明为CHAR(10)，那么此时该变量实际为CHAR(10 BYTE)，当把BYTE改成CHAR后，再去调用该存储过程，会报 ORA-06502 PL/SQL Numeric or value error错误，因为虽然环境使用的是CHAR语义，但是存储过程里还是使用的BYTE语义，此时需要重要编译该存储过程，会重新读取当前SESSION的值，写入代码中。鉴于此，建议在书写存储过程的时候显式声明其语义(CHAR(10 CHAR))，以免不必要的麻烦。