19c LAST_VALUE for sequences is incorrectly calculated when next cache of numbers required
(Doc ID 2797098.1)
Last updated on APRIL 17, 2023
Applies to:Oracle Database - Enterprise Edition - Version 18.104.22.168.0 and later
Information in this document applies to any platform.
When importing sequences used by automatic PK generation we have found a new problem with sequences in 19c.
After multiple calls to NEXTVAL such that the sequence has to cache a fresh set of values the size of that cache and hence the LAST_VALUE (as seen in user_sequences) is not set correctly.
It becomes much bigger than it should and the problem become worse at each increase.
Below are the last_number values we were trying to get to in the left hand column.
The right hand column is the LAST_VALUE we see when we have obtained the value on the left via NEXTVAL
These had a cache size of 20
With the example code to replicate this issue as attached we see these changes in LAST_NUMBER as NEXTVAL is obtained forcing a fresh cache of value.
Cache of 20
Cache of 5
Cache of 3
It is almost like the next LAST_VALUE and hence the next cache size is being calculated using string concatenation rather than actual math.
This same code works correctly in 12.2 and lower.
Issue is only on 19c and not in 12c and 18c as tested.
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!
In this Document