一次seq大小和列长度不匹配的问题

来源：互联网发布：华图直播软件编辑：程序博客网时间：2024/05/21 11:07

问题描述

周末的某个下午，接到电话报错，业务已不能正常办理

电话联系业务人员，得知系统报错：ora-01438 sieble.s_srv_req_escl_t2 , line 25 error triger

原因分析

1，查看ora-01438的具体意思，基本意思是插入的值大于列的长度

[oracle@dtydb0 ~]$ oerr ora 0143801438, 00000, "value larger than specified precision allowed for this column"// *Cause: When inserting or updating records, a numeric value was entered//         that exceeded the precision defined for the column.// *Action: Enter a value that complies with the numeric column's precision,//          or use the MODIFY option with the ALTER TABLE command to expand//          the precision.

2，查看报错的对象的具体内容

select object_name,OWNER,CREATED,LAST_DDL_TIME,OBJECT_TYPE from dba_objects where object_name = 'S_SRV_REQ_ESCL_T2';

该对象是一个trigger

select dbms_metadata.get_ddl('TRIGGER','S_SRV_REQ_ESCL_T2','SIEBEL') from dual;.....   insert into SIEBEL.S_ESCL_REQ (REQ_ID, BT_ROW_ID, RULE_ID, TBL_NAME, CREATED_BY,GROUP_ID)      values (S_ESCL_REQ_S.nextval, :new.ROW_ID,'1-JK19O',              'S_SRV_REQ', :new.LAST_UPD_BY, '1-ZK3');............

第25行是一个insert语句，插入的数据涉及到sequence S_ESCL_REQ_S

3，查看sequence

SQL> SELECT * FROM DBA_SEQUENCES WHERE SEQUENCE_NAME = 'S_ESCL_REQ_S';SEQUENCE_OWNER                 SEQUENCE_NAME                   MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER------------------------------ ------------------------------ ---------- ---------- ------------ - - ---------- -----------SIEBEL                         S_ESCL_REQ_S                            1 1.0000E+27            1 N Y         20  1000028019

4，查看表的情况NUMBER(9)和sequence和LAST_NUMBER（10位了已经），明显不匹配了，导致业务报错

SQL> desc SIEBEL.S_ESCL_REQ Name                                      Null?    Type ----------------------------------------- -------- ---------------------------- REQ_ID                                    NOT NULL NUMBER(9) CREATED                                            DATE CREATED_BY                                         VARCHAR2(15 CHAR) BT_ROW_ID                                          VARCHAR2(15 CHAR) GROUP_ID                                           VARCHAR2(15 CHAR) RULE_ID                                            VARCHAR2(15 CHAR) TBL_NAME                                           VARCHAR2(30 CHAR)

问题处理
5，为了尽快解决问题，联系业务部门确认，重置了sequence的大小，问题解决

先删除，在创建

   DROP SEQUENCE SIEBEL.S_ESCL_REQ_S   CREATE SEQUENCE  "SIEBEL"."S_ESCL_REQ_S"  MINVALUE 1 MAXVALUE 1.00000000000000E+27 INCREMENT BY 1 START WITH 2 CACHE 20 ORDER NOCYCLE

6，为防止类似问题，特增加了sequence的健康脚本，利用率超过一定范围，系统发告警短信通知，但却解决本次问题的问题，此类问题只能应用梳理了

select util seq_pct_used，sequence_owner，SEQUENCE_NAME  from (select round(((a.last_number - min_value) / (a.max_value- min_value)) * 100, 2) util,a.*          from dba_sequences a         where sequence_owner not in ('SYS', 'SYSTEM')           and cycle_flag = 'N'         order by util desc)where rownum < 2