關閉資料庫會導致cache的sequence值丟失嗎？

wei-xh發表於2014-07-10
資料庫
sequence的值為什麼不連續了，換一個說法就是什麼情況下sequence的值會丟失。
有幾種情況：
先看第一種情況：關閉資料庫會導致cache的sequence值丟失嗎？
做個實驗看看。
ORACLE12>conn test/test
Connected.



test@ORACLE12>create sequence seq_a;


Sequence created.



test@ORACLE12>col SEQUENCE_NAME for a25
test@ORACLE12>select sequence_name, cache_size from all_sequences where sequence_name = 'SEQ_A';


SEQUENCE_NAME             CACHE_SIZE
------------------------- ----------
SEQ_A                             20


test@ORACLE12>select seq_a.nextval from dual;


   NEXTVAL
----------
         1


test@ORACLE12>/


   NEXTVAL
----------
         2

我們乾淨的關閉資料庫，看看cache的sequence是否會丟失。
test@ORACLE12>conn / as sysdba
Connected.
ORACLE12>shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE12>startup
ORACLE instance started.


Total System Global Area 6046453760 bytes
Fixed Size                  3481176 bytes
Variable Size            2801795496 bytes
Database Buffers         3221225472 bytes
Redo Buffers               19951616 bytes
Database mounted.
Database opened.
ORACLE12>conn test/test
Connected.
test@ORACLE12>select seq_a.nextval from dual;


   NEXTVAL
----------
         3

有點出乎我的意外，sequence的next值是從3開始的，sequence的值並沒有丟失，看來乾淨的關閉資料庫不會導致sequence值丟失。
看看shutdown abort是否會導致cache的sequence值丟失。

test@ORACLE12>conn / as sysdba
Connected.
ORACLE12>startup force
ORACLE instance started.


Total System Global Area 6046453760 bytes
Fixed Size                  3481176 bytes
Variable Size            2801795496 bytes
Database Buffers         3221225472 bytes
Redo Buffers               19951616 bytes
Database mounted.
Database opened.


ORACLE12>conn test/test
Connected.
test@ORACLE12>select seq_a.nextval from dual;


   NEXTVAL
----------
        23
sequence的next值已經從23開始了，丟失了4-22範圍的sequence的值，看來shutdown abort值會導致cache的sequence值丟失。

第二種情況，flush shared pool會導致cache的sequence值丟失
test@ORACLE12>select seq_a.nextval from dual;


   NEXTVAL
----------
        23


test@ORACLE12>/


   NEXTVAL
----------
        24


test@ORACLE12>alter system flush shared_pool;


System altered.


test@ORACLE12>select seq_a.nextval from dual;


   NEXTVAL
----------
        43


第三種情況，事務回滾導致sequence值丟失
sequence值一旦獲取，就必須使用或扔棄，即使事務回滾，已經被獲取的sequence不能被重新使用。
這種情況即使把sequence的cache屬性設定為nocache也無濟於事。本文提到的前兩種情況都可以透過把sequence的cache屬性設定為nocache來避免。

這裡需要補充一個merge裡呼叫seq的特殊情況：

create table test1 as select * from dba_objects;

create table test2 as select * from test1;

create sequence seq_test1;
select seq_test1.nextval from dual;


   NEXTVAL
----------
         1

MERGE INTO test1 T
USING test2 Y
ON (T.object_ID = Y.object_ID)
WHEN MATCHED THEN
  UPDATE SET T.object_name = Y.object_name
WHEN NOT MATCHED THEN
  INSERT (object_ID, object_name) VALUES (seq_test1.NEXTVAL, 333);

15357 rows merged.
上面的merge語句並不會觸發not matched部分的程式碼，但是我們再次檢視seq_test1的next值，已經增大到15359了，相當於每一條T.object_ID = Y.object_ID符合的記錄，都要觸發seq_test1.NEXTVAL，我們在使用merge語句的時候需要注意到這個問題，有可能會遭遇表裡的seq值經常斷號的問題。

select seq_test1.nextval from dual;

   NEXTVAL
----------
     15359

select count(*) from test1;
  COUNT(*)
----------
     15357
1 row selected.
select count(*) from test2;
  COUNT(*)
----------
     15357
1 row selected.

解決這個問題的辦法可以參考：
http://blog.itpub.net/10356975/viewspace-680338/
http://blog.csdn.net/smartyidiot/article/details/8196449
http://www.itpub.net/thread-1874494-1-1.html

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22034023/viewspace-1215283/，如需轉載，請註明出處，否則將追究法律責任。

相關文章