问题描述
业务前台报错插入工单失败:ORA-01438
ORA-01438: 值大于此列指定的允许精确度
后来和业务人员确认是一个number(a,b)类型的字段,这个字段引用了一个sequence,而这个sequence的最大值已经超过了该字段的精度,后通过重置sequence,并设置了maxvalue。如果不能重置sequence,那就只能修改字段精度了。
1、ora-01438不记录到alert中
专家解答
1、ora-01438不记录到alert中
sys@JYPUBLIC>create table a_test (id number(4,2)); Table created. sys@JYPUBLIC>insert into a_test values(1111); insert into a_test values(1111) * ERROR at line 1: ORA-01438: value larger than specified precision allowed for this column SQL> insert into a_test values(111); insert into a_test values(111) * ERROR at line 1: ORA-01438: value larger than specified precision allowed for this column SQL> insert into a_test values(11); 1 row created. SQL> insert into a_test values(11.11111); 1 row created. sys@JYPUBLIC>drop table a_test purge; Table dropped. sys@JYPUBLIC>exit oracle@jyggdb1:/oracle/app/oracle/diag/rdbms/trace$ cat alert_1.log Tue Apr 01 22:09:48 2014 create table a_test (id number(4,2)) Tue Apr 01 22:10:17 2014 drop table a_test purge
2、查看数据库中所有number(a,b)类型字段
select distinct owner,TABLE_NAME,COLUMN_NAME,DATA_PRECISION,DATA_SCALE from dba_tab_columns where DATA_TYPE='NUMBER' and DATA_PRECISION<>0 and DATA_SCALE<>0 order by 1,2; sys@JYCRM>col MAX_VALUE for 999999999999999999999 sys@JYCRM>r 1* select * from dba_SEQUENCES where SEQUENCE_NAME='I_WK_OPER$SEQ' SEQUENCE_OWNER SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER ------------------------------ ------------------------------ ---------- ---------------------- ------------ - - ---------- ----------- S01 I_WK_OPER$SEQ 1 999999999999 1 N N 200 1000298052
解决:a、扩展精度,b、调整sequence的last_number
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。