序列sequence中的cache問題

標籤：os   ar   strong   for   資料   sp   art   問題   on   

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中預存的序列值不會丟失。

序列sequence中的cache問題