Question:
The real limit of proc_event value set for the oracle instance is 2147483648 and we observed that proc_event number in this oracle instance has reached the 1502936274. how to reset this value ?
Answer:
From ujo_bump_oid stored procedure
when the value is 2147483647 then the value becomes 100
so when it reaches max int, it will rollover to 100 and start using the 100
and then increment.
Here is the Oracle SP from code:
CREATE OR REPLACE
FUNCTION ujo_bump_oid (
i_field varchar2)
return number as
l_oid number(10);
l_start_datetime date;
BEGIN
IF (i_field = 'eoid') THEN
UPDATE ujo_next_oid
SET oid = (CASE WHEN oid != 99999999 THEN oid+1 ELSE 10000000 END)
WHERE field = i_field
RETURNING (CASE WHEN oid != 10000000 THEN oid-1 ELSE 99999999 END) INTO l_oid;
ELSE
UPDATE ujo_next_oid
SET oid = (CASE WHEN oid != 2147483647 THEN oid+1 ELSE 100 END)
WHERE field = i_field
RETURNING (CASE WHEN oid != 100 THEN oid-1 ELSE 2147483647 END) INTO l_oid;
IF (i_field = 'evt_num') AND (l_oid = 100) THEN
/* loop until next time tick to prevent same timestamp */
l_start_datetime := sysdate;
LOOP
EXIT WHEN (to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') != to_char(l_start_datetime, 'yyyy-mm-dd hh24:mi:ss'));
END LOOP;
END IF;
END IF;
return l_oid;
END;
/
grant execute on ujo_bump_oid to ujoadmin