导致cache的sequence值丢失的情况

转自http://blog.itpub.net/22034023/viewspace-1215283/

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