ORACLE SEQUENCE跳號總結

    在ORACLE資料庫中，序列（SEQUENCE）是使用非常頻繁的一個資料庫對象，但是有時候會遇到序列（SEQUECNE）跳號(skip sequence numbers)的情形，那麼在哪些情形下會遇到跳號呢？

事務回滾引起的跳號

不管序列有沒有CACHE、事務回滾這種情況下，都會引起序列的跳號。如下實驗所示：

SQL> create sequence my_sequence

  2  start with 1

  3  increment by 1

  4  maxvalue 99999

  5  nocache;

Sequence created.

SQL>  create table test(id number(10), name varchar2(32));

Table created.

SQL> insert into test

  2  select my_sequence.nextval , 'kerry' from dual;

1 row created.

SQL> 

SQL> rollback;

Rollback complete.

SQL> select my_sequence.nextval from dual;

   NEXTVAL

----------

         3

SQL> 

併發訪問序列引起的跳號

併發訪問序列引起的跳號，其實不算真正的跳號，而只是邏輯跳號，只是序列值被其它併發會話使用了。我們來構造一起併發訪問序列引起的跳號，我們開啟兩個會話視窗，迴圈獲取序列的值，模擬併發出現的場景。

會話視窗A：

exec dbms_lock.sleep(2); --延遲2秒執行，根據你實驗情況調整

/

begin

    for i in 1 .. 2000 loop

        dbms_output.put_line(my_sequence.nextval);

    end loop;

end;

/

會話視窗B：

spool test.txt;

begin

    waitfor delay '00:00:10';

    for i in 1 .. 2000 loop

        dbms_output.put_line(my_sequence.nextval);

    end loop;

end;

/

spool off; 

如下所示，我構造的實驗當中，你會看到序列的跳號情況。

FLUSH SHARED_POOL會導致CACHE的序列跳號

實驗測試如下所示（序列的CACHE值必須大於0），當然正常情況下，很難遇到這種情況。

SQL> select test.my_sequence.nextval from dual;

   NEXTVAL

----------

     17004

SQL> alter sequence test.my_sequence cache 40;

Sequence altered.

SQL> select test.my_sequence.nextval from dual;

   NEXTVAL

----------

     17005

SQL> alter system flush share_pool;

alter system flush share_pool

*

ERROR at line 1:

ORA-02000: missing SHARED_POOL/BUFFER_CACHE/GLOBAL CONTEXT keyword

SQL> alter system flush shared_pool;

System altered.

SQL> select test.my_sequence.nextval from dual;

   NEXTVAL

----------

     17045

資料庫實例異常關閉導致跳號

如下實驗所示，當資料庫使用shutdown abort命令關閉後，重新啟動實例，序列緩存在shared pool裡面沒有用過的值都沒有了。一下子從17045跳到17085

SQL> select test.my_sequence.currval from dual;