The size of one seq does not match the column length.

One seq size and Column Length mismatch Problem description one afternoon of the weekend, received a call error, the business has been unable to handle the phone contact business staff, it is known that the system error: ora-01438 sieble. s_srv_req_escl_t2, line 25 error triger Cause Analysis www.2cto.com 1, view the specific meaning of the ora-01438, the basic meaning is that the inserted value is greater than the length of the column [SQL] [oracle @ dtydb0 ~] $ Oerr ora 01438 01438,000 00, "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. view the specific content of the error object [SQL] select object_name, OWNER, CREATED, LAST_DDL_TIME, OBJECT_TYPE from dba_objects where object_name ='s _ SRV_REQ_ESCL_T2 '; this object is a trigger [SQL] 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 '); www.2cto.com ............ row 25th is an insert statement. The inserted data involves sequence S_ESCL_REQ_S 3. View sequence [SQL] 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.20.e + 27 1 n y 20 1000028019 4. Check whether the NUMBER (9) and sequence and LAST_NUMBER (10 digits already exist) of the table obviously do not match, [SQL] SQL> desc SIEBEL. s_ESCL_REQ Name Null? Type direction -------- ---------------------------- 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) troubleshooting
5. In order to solve the problem as soon as possible, contact the business department to confirm and reset the sequence SIZE. Delete the problem first and create www.2cto.com [SQL] DROP SEQUENCE SIEBEL. s_ESCL_REQ_S create sequence "SIEBEL ". "S_ESCL_REQ_S" MINVALUE 1 MAXVALUE 1.20.0000000000e + 27 increment by 1 start with 2 CACHE 20 order nocycle 6. To prevent similar problems, the sequence health script is added, and the utilization rate exceeds a certain range, the system sends an alert text message but solves this problem. This problem can only be solved by [SQL] select util seq_pct_used, sequence_owner, SEQUENCE_NAME from (select round (. last_number-min_value)/(. max_value-min_value) * 100, 2) util,. * from dba_sequences a where sequence_owner not in ('sys ', 'system') and cycle_flag = 'n' order by util desc) where rownum <2