Cache problems in sequence Sequence

The sequence creation syntax in Oracle is as follows:

CREATE SEQUENCE [ schema. ] sequence   [ { INCREMENT BY | START WITH } integer   | { MAXVALUE integer | NOMAXVALUE }   | { MINVALUE integer | NOMINVALUE }   | { CYCLE | NOCYCLE }   | { CACHE integer | NOCACHE }   | { ORDER | NOORDER }   ]...;

In the sequence creation statement,

The cache clause preallocates 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, the database reads another set of numbers into the cache.

The database might skip sequence numbers if you choose to cache a set of sequence numbers. for example, when an instance abnormally shuts down (for example, when an instance failure occurs or a shutdown abort statement is issued), sequence numbers that have been cached but not used are lost.

-- Let's test the sequence as follows:

SQL> Create sequence S1
2 start with 10
3 increment by 10
4 maxvalue 1000000000000000000000
5 cycle
6 Cache 10;

SQL> select s1.nextval from dual;

Nextval
----------
10

SQL> shutdown abort

SQL> startup

SQL> select s1.nextval from dual;

Nextval
----------
110

SQL>/

Nextval
----------
120

SQL> shutdown immediate

SQL> startup

SQL> select s1.nextval from dual;

Nextval
----------
130

It can be seen that when the database is shut down abnormally, all the pre-stored sequence values in the cache are lost. In this example, 10 values are pre-stored, from 10 to 100 ,. After the database is restarted, the next sequence value starts from 120. When the database is shut down normally, the pre-stored sequence values in the cache will not be lost.

Cache problems in sequence Sequence