proc_event reach the limit

Document created by zgofa01 Employee on Jul 5, 2016
Version 1Show Document
  • View in full screen mode

 

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

Attachments

    Outcomes