序列sequence中的cache问题

最新推荐文章于 2024-03-28 10:52:47 发布

原创最新推荐文章于 2024-03-28 10:52:47 发布 · 677 阅读

0 ·

CC 4.0 BY-SA版权

本文介绍了Oracle数据库中序列(sequence)的创建语法及其CACHE子句的作用。通过实例演示了当数据库异常关闭时，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