19c LAST_VALUE for sequences is incorrectly calculated when next cache of numbers required (Doc ID 2797098.1)

Last updated on JULY 20, 2024

Applies to:

Symptoms

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
41 221
11801 22221
521 2221
16284 22221
5081 22221

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
1
21
221

Cache of 5
1
56
556

Cache of 3
1
4
34
334
3334

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. 

Changes

 Issue is only on 19c and not in 12c and 18c as tested.

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.