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

问题描述

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

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

原因分析

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

[oracle@dtydb0 ~]$ oerr ora 01438
01438, 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