Hey everyone,

Hope you’re doing well.

This week it happened a “good” discussion in a WhatsApp Group for a DBA Brazilian Community (DBA Brasil).

One colleague was facing an “inexplicable” situation.

He had an object SEQUENCE, configured with INCREMENT_BY to 1 and CACHE_SIZE to 20 (default).

Then he executed this: select seq_cliente.nextval from dual;

The value of the sequence goes to 281, which is expected, no issues. See in the screenshot below:

 

After this, he simulated an action that application was doing, probably by any mistake:

create sequence seq_cliente;

The following error was triggered:

ORA-00955: name is already used by an existing object.

Hum, OK, no news until now, right?

Well, then he executed again the select seq_cliente.nextval from dual, and then the value jumped from 281 to 301:

 

So, it jumped by 20 numbers, not respecting the nextval, which was supposed to be 282.

How this happened?

Well, I made a simple test case. See below:

Here, I created the sequence:

SQL> create sequence test;
Sequence created.

Here, I am checking the INCREMENT_BY, CACHE_SIZE and LAST_NUMBER:



SQL> select sequence_name,increment_by,cache_size,last_number from user_sequences where sequence_name='TEST';

SEQUENCE_N INCREMENT_BY CACHE_SIZE LAST_NUMBER
---------- ------------ ---------- -----------
TEST 1 20 1


Then I ran the nextval two times:


SQL> select test.nextval from dual;
NEXTVAL
----------
1


SQL> select test.nextval from dual;
NEXTVAL
----------
2


I did verify again the INCREMENT_BY, CACHE_SIZE and LAST_NUMBER.


SQL> select sequence_name,increment_by,cache_size,last_number from user_sequences where sequence_name='TEST';


SEQUENCE_N INCREMENT_BY CACHE_SIZE LAST_NUMBER
---------- ------------ ---------- -----------
TEST 1 20 21


Well, so far so good! Our LAST_NUMBER is 21. Why?


If you check the documentation oficial:


 




So, as sequence is using CACHE, Oracle already preallocated the value and written them to disk, but is STILL relying in the values that are in memory (cache).


Let’s simulate an error, let’s try to recreate the sequence:


SQL> create sequence test;

create sequence test

*

ERROR at line 1:

ORA-00955: name is already used by an existing object


OK, nothing different than expected.


Let’s run again the NEXTVAL:


SQL> select test.nextval from dual;
NEXTVAL
----------
3


SQL> select test.nextval from dual;
NEXTVAL
----------
4


SQL> select test.nextval from dual;
NEXTVAL
----------
5


SQL> select test.nextval from dual;
NEXTVAL
----------
6


SQL> select test.nextval from dual;
NEXTVAL
----------
7


 


Oracle Database working as expected, right?


One more test, let’s do the flush in Shared Pool:


SQL> alter system flush shared_pool;

System altered.


Let’s run again the NEXTVAL:


SQL> select test.nextval from dual;

NEXTVAL

———-

21


Hey, how is this possible?


Why sequence jumped from 7 to 21?


This is also explained by the documentation inicial:




 


So, it happened a “system failure”, all values that are in cache and are not used by commited transactions, are lost.


When this happens, the cache of sequence is flushed and when you try to allocate the next value, it will get the value that was on disk as defined by LAST_NUMBER.


Just a reminder that this happened not only by a system failures, but mainly because of Shared Pool flush because the cache sequence values are stored in the Data Dictionary Cache, one of the caches of Shared Pool:




Here, another test case:


SQL> select test.nextval from dual;
NEXTVAL
----------
41


SQL> select test.nextval from dual;
NEXTVAL
----------
42


SQL> select test.nextval from dual;
NEXTVAL
----------
43


And then, here, I triggered one more flush of Shared Pool:


SQL> alter system flush shared_pool;
System altered.


SQL> select test.nextval from dual;
NEXTVAL
----------
61


 




 


Edit: March, 2nd, 2023:


 




After a good discussion with Rodrigo Jorge, Senior Product Manager from Upgrades, Migrations, Patching and Utilities team and also with Alex Zaballa, I did an another test case, now in an environment with some workload.


I’ve created an user in this DB only for the test, but DB has a considerable workload from another users…


SQL> create sequence test ;

Sequence created.

SQL>

SQL> select sequence_name,last_number from user_sequences;

SEQUENCE_N LAST_NUMBER

---------- -----------

TEST 1

SQL> select test.nextval from dual;

NEXTVAL

----------

1

SQL> select sequence_name,last_number from user_sequences;

SEQUENCE_N LAST_NUMBER

---------- -----------

TEST 21

SQL> create sequence test ;

create sequence test

*

ERROR at line 1:

ORA-00955: name is already used by an existing object

SQL> select sequence_name,last_number from user_sequences;

SEQUENCE_N LAST_NUMBER

---------- -----------

TEST 21

SQL> select test.nextval from dual;

NEXTVAL

----------

21

SQL>

SQL>

SQL>

SQL> select test.nextval from dual;

NEXTVAL

----------

22

SQL> select sequence_name,last_number from user_sequences;

SEQUENCE_N LAST_NUMBER

---------- -----------

TEST 41


This concludes that the error ORA-00955 that happens when try to recreate an existing sequence can jump the values by flushing the cache. The interesting is that not all times this happens, there is something related to the workload.




Apart Oracle saying that this is an expected behavior, I couldn’t agree… this is more related to a bug behavior than an expected behavior.




Hope it helps!


Peace.


 Vinicius