Effects Of Sequence Aging Out Of Shared Pool

Like any other database object in shared pool sequence could also be aged out or flushed out of the shared pool to make room for other objects. When this happens values return by the sequence may not be in the expected order.

SQL> create sequence mseq ;

SQL> select * from user_sequences;
SEQUENCE_NAME    MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
--------------- ---------- ---------- ------------ - - ---------- -----------
MSEQ                     1 1.0000E+28            1 N N         20           1

Take the first value from the sequence and examine the last number

SQL> select mseq.nextval from dual;

   NEXTVAL
----------
         1

SQL> select * from user_sequences;

SEQUENCE_NAME    MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
--------------- ---------- ---------- ------------ - - ---------- -----------
MSEQ                     1 1.0000E+28            1 N N         20          21

At this stage if the sequence is aged out of the shared pool (in this test case by flushing the shared pool) the next value returned would be 21 instead of 2. As sysdba run

SQL> alter system flush shared_pool;

and then as owner of the sequence

SQL> select mseq.nextval from dual;

   NEXTVAL
----------
        21

SQL> select * from user_sequences;

SEQUENCE_NAME    MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
--------------- ---------- ---------- ------------ - - ---------- -----------
MSEQ                     1 1.0000E+28            1 N N         20          41

This creates gaps in the values returned by the sequences and it only affects the cached sequences and nocache, order sequences are not affected

SQL> create sequence mseq nocache order;

SQL> select mseq.nextval from dual;

   NEXTVAL
----------
         1

SQL> select * from user_sequences;

SEQUENCE_NAME    MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
--------------- ---------- ---------- ------------ - - ---------- -----------
MSEQ                     1 1.0000E+28            1 N Y          0           2

Flush the shared pool

SQL> alter system flush shared_pool;

Sequence returns the next value 2 and flushing shared pool has no effect

SQL> select * from user_sequences;

SEQUENCE_NAME    MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
--------------- ---------- ---------- ------------ - - ---------- -----------
MSEQ                     1 1.0000E+28            1 N Y          0           2

SQL> select mseq.nextval from dual;

   NEXTVAL
----------
         2

SQL>  select * from user_sequences;

SEQUENCE_NAME    MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
--------------- ---------- ---------- ------------ - - ---------- -----------
MSEQ                     1 1.0000E+28            1 N Y          0           3

One way to prevent is to pin the sequence in the shared pool

SQL> create sequence mseq ;

SQL> select mseq.nextval from dual;

   NEXTVAL
----------
         1

SQL> select * from user_sequences;

SEQUENCE_NAME    MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
--------------- ---------- ---------- ------------ - - ---------- -----------
MSEQ                     1 1.0000E+28            1 N N         20          21

Pin the sequence with

SQL> exec dbms_shared_pool.keep('ASANGA.MSEQ','Q');

and flush the shared pool

SQL> alter system flush shared_pool;

Ordered values are returned from the sequence without a gap

SQL> select mseq.nextval from dual;

   NEXTVAL
----------
         2

SQL> /

   NEXTVAL
----------
         3

SQL> select * from user_sequences;

SEQUENCE_NAME    MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
--------------- ---------- ---------- ------------ - - ---------- -----------
MSEQ                     1 1.0000E+28            1 N N         20          21