Oracle sequence and cache option

Let's first see what is the syntax of a sequence;

CREATE SEQUENCE sequence_name
    MINVALUE value
    MAXVALUE value
    START WITH value
    INCREMENT BY value
    CACHE value;

In order to get the current value of a sequence, we have currval and in order to get the next value of a sequence, we have nextval.

Let's say we create a sequence using the following syntax;

CREATE SEQUENCE pubnum
    MAXVALUE 1000
    START WITH 1
    INCREMENT BY 1;

now, if I try to get the current and next sequence number using a single query, one might use a query similar to following;

select pubnum.currval, pubnum.nextval from dual;

However, the above query simply increments the sequence and gives you the incremented value for both current value and next value. This shows that we have to use some other mechanism to do this. Fortunately, oracle stores the details of sequences in a table called user_sequences which stores the details of all sequences created. This table has one column by the name of "LAST_NUMBER" which tells you the next number to be generated from the sequence. Now, let's say the last sequence number generated from pubnum sequence 2 and we execute the following query;

select last_number from user_sequences where sequence_name = 'PUBNUM';

The above should return 3, however, the above returns 21 which is surprising. The reason is hidden in the create statement of the sequence. Remember, the CACHE option of the sequence create statement. The CACHE option pre-allocates a set of sequence numbers and keeps them in memory so that sequence numbers can be accessed faster. When the last of the sequence numbers in the cache has been used, Oracle reads another set of numbers into the cache. However, when you don't mention any value for the CACHE option, the default value of 20 is used. Now, read the bold part again. What Oracle does is that it stores the next 20 numbers in cache(assuming cache size is 20). The current value of the sequence was 2, however, due to the 20 numbers placed in cache, the last_number in the user_sequences table gave us 21 instead of 3. The value of last_number in the user_sequences will remain 21 till the actual sequence reaches 20. As soon as the current value of the sequence becomes 21, the last_number will start pointing to 41 (it will prefetch next 20 numbers). Hence, if you want this to work, you have to mention NOCACHE option during the sequence creation. Look at the following updated syntax;

CREATE SEQUENCE pubnum1
    MAXVALUE 1000
    START WITH 1
    INCREMENT BY 1

    NOCACHE;

Let's suppose the current value of the pubnum1 is 3. If you run the following query;

select last_number from user_sequences where sequence_name = 'PUBNUM1';

Now, the above will return 4 which is the correct value. So, in order to get current value and next value of the sequence using a single query, we can use the following query;


select last_number - 1 as "Current value", last_number as "Next Value" from user_sequences where sequence_name = 'PUBNUM1';