序列sequence中的cache问题

Oracle中序列Sequence的创建语法如下：

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

在sequence的创建语句中，

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.

--下面我们来做个试验测试一下，创建序列如下：

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

由此可见，在数据库异常关闭时，cache中预存的序列值全部丢失，在本例中预存了10个值，从10到100,。重新启动数据库后，下一个序列值从120开始。而数据库在正常关闭时，cache中预存的序列值不会丢失。