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

When the sequence is in cache, last_number represent the number keep by oracle. When not, it represents the last sequence use by Oracle. With your alter command, you change the settings of the sequence, so Oracle flush it's "sequence cache"

Here a simple example

SQL> drop sequence test;
Sequence dropped

SQL> create sequence test cache 20;
Sequence created

SQL> select last_number  from user_sequences where sequence_name='TEST';
LAST_NUMBER
-----------
          1

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

SQL>  select last_number  from user_sequences where sequence_name='TEST';
LAST_NUMBER
-----------
         21

SQL> alter sequence test CACHE 5000;
Sequence altered

SQL>  select last_number  from user_sequences where sequence_name='TEST';
LAST_NUMBER
-----------
          2

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

SQL>  select last_number  from user_sequences where sequence_name='TEST';
LAST_NUMBER
-----------
       5002

SQL> 

Answer 2

This post is for changing Last Number :)

Let us assume LAST_NUMBER in seq xyz is 3953 and you want to change it to 205233, then you need to increment the sequence by (205233 - 3953 = 201,280) and then increment it by 1. that can be achieved by below statements:

to change the LAST_NUMBER to

ALTER SEQUENCE xyz INCREMENT BY 201280;
SELECT xyz.nextval from dual;
ALTER SEQUENCE xyz INCREMENT BY 1;

Now the LAST_NUMBER would be updated as 205233