LAST_NUMBER on oracle sequence

Question

I have a sequence SEQ_PAGE_ID

SEQUENCE_NAME   INCREMENT_BY  CACHE_SIZE   LAST_NUMBER            
-------------------------------------------------------
SEQ_PAGE_ID      1              20          2222292456 

To change the CACHE_SIZE, I used below script,

alter sequence SEQ_PAGE_ID CACHE 5000;

When I checked the query,

select ... from user_sequences where sequence_name  = 'SEQ_PAGE_ID';


SEQUENCE_NAME   INCREMENT_BY  CACHE_SIZE    LAST_NUMBER            
-------------------------------------------------------
SEQ_PAGE_ID      1              5000          2222292447 

The LAST_NUMBER changed from 2222292456 to 2222292447. Is this happened due to the alter script?

Answer 1

This is normal, yes. From the documentation for the all_sequences data dictionary view, last_number is:

Last sequence number written to disk. If a sequence uses caching, the number written to disk is the last number placed in the sequence cache. This number is likely to be greater than the last sequence number that was used.

This can be recreated with a fresh sequence:

SQL> create sequence SEQ_PAGE_ID start with 2222292436 increment by 1 cache 20;

sequence SEQ_PAGE_ID created.

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

SEQUENCE_NAME                  INCREMENT_BY CACHE_SIZE LAST_NUMBER
------------------------------ ------------ ---------- -----------
SEQ_PAGE_ID                               1         20  2222292436 

SQL> select SEQ_PAGE_ID.nextval from dual;

   NEXTVAL
----------
2222292436 

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

SEQUENCE_NAME                  INCREMENT_BY CACHE_SIZE LAST_NUMBER
------------------------------ ------------ ---------- -----------
SEQ_PAGE_ID                               1         20  2222292456 

The last_number jumped up by the cache size, which is normal.

SQL> alter sequence SEQ_PAGE_ID CACHE 5000;

sequence SEQ_PAGE_ID altered.

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

SEQUENCE_NAME                  INCREMENT_BY CACHE_SIZE LAST_NUMBER
------------------------------ ------------ ---------- -----------
SEQ_PAGE_ID                               1       5000  2222292437 

The last_number goes down, but now reflects the actual last sequence number generated. The DDL has (apparently) caused the data written to disk to be updated to reflect what happens to be the current value, rather than the top of the cache - either the old 20-value cache or the new 5000-value cache. In your case you got 2222292447, which just means you were ten values further through the cache than I was when I ran the alter.

The value saved to disk is largely there so that if the database crashes it knows where to pick up from. On restart the sequence will start generating numbers from the recorded last_number. During normal running it doesn't need to refer back to that, it just updates the value on disk when new values are cached. This prevents sequence numbers being reissued after a crash, without needing to do expensive (slow) locking to maintain the value in real time - which is what the cache is there to avoid, after all.

There would only be a problem if the last_value was lower than an actual generated sequence, but that can't happen. (Well, unless the sequence is set to cycle).

SQL> select SEQ_PAGE_ID.nextval from dual;

   NEXTVAL
----------
2222292437 

The next sequence number generated follows on from the last one before the cache size change; it hasn't reused an old value as you might have been worried about from the dictionary value.

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

SEQUENCE_NAME                  INCREMENT_BY CACHE_SIZE LAST_NUMBER
------------------------------ ------------ ---------- -----------
SEQ_PAGE_ID                               1       5000  2222297437 

The last_number now shows the previous stored value incremented by the cache size of 5000. What is in the data dictionary now won't change again until we've consumed all 5000 values form the cache, or something happens elsewhere that affects it - the database being bounced, the sequence being altered again, etc.