oracle获取序列跳号,ORACLE SEQUENCE跳号总结

在ORACLE数据库中，序列(SEQUENCE)是使用非常频繁的一个数据库对象，但是有时候会遇到序列(SEQUECNE)跳号(skip sequence numbers)的情形，那么在哪些情形下会遇到跳号呢？

事务回滚引起的跳号

不管序列有没有CACHE、事务回滚这种情况下，都会引起序列的跳号。如下实验所示：

SQL> create sequence my_sequence2  start with 13  increment by 14  maxvalue 999995  nocache;Sequence created.SQL>  create table test(id number(10), name varchar2(32));Table created.SQL> insert into test2  select my_sequence.nextval , 'kerry' from dual;1 row created.SQL>SQL> rollback;Rollback complete.SQL> select my_sequence.nextval from dual;NEXTVAL----------3SQL>

并发访问序列引起的跳号

并发访问序列引起的跳号，其实不算真正的跳号，而只是逻辑跳号，只是序列值被其它并发会话使用了。我们来构造一起并发访问序列引起的跳号，我们开启两个会话窗口，循环获取序列的值，模拟并发出现的场景。

会话窗口A：

exec dbms_lock.sleep(2); --延迟2秒执行，根据你实验情况调整/beginfor i in 1 .. 2000 loopdbms_output.put_line(my_sequence.nextval);end loop;end;/

会话窗口B：

spool test.txt;beginwaitfor delay '00:00:10';for i in 1 .. 2000 loopdbms_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----------17004SQL> alter sequence test.my_sequence cache 40;Sequence altered.SQL> select test.my_sequence.nextval from dual;NEXTVAL----------17005SQL> alter system flush share_pool;alter system flush share_pool*ERROR at line 1:ORA-02000: missing SHARED_POOL/BUFFER_CACHE/GLOBAL CONTEXT keywordSQL> 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;CURRVAL----------17045SQL> select object_id from dba_objects where object_name=upper('my_sequence');OBJECT_ID----------97760SQL> select increment$, minvalue, maxvalue,highwater, cache2  from seq$ where obj#=97760;INCREMENT$   MINVALUE   MAXVALUE  HIGHWATER      CACHE---------- ---------- ---------- ---------- ----------1          1      99999      17085         40SQL> shutdown abort;ORACLE instance shut down.SQL> startup;ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instanceORACLE instance started.Total System Global Area 1620115456 bytesFixed Size                  2213816 bytesVariable Size            1258293320 bytesDatabase Buffers          352321536 bytesRedo Buffers                7286784 bytesDatabase mounted.Database opened.SQL> select test.my_sequence.currval from dual;select test.my_sequence.currval from dual*ERROR at line 1:ORA-08002: sequence MY_SEQUENCE.CURRVAL is not yet defined in this sessionSQL> select test.my_sequence.nextval from dual;NEXTVAL----------17085SQL>

另外，我们也来看看正常关闭数据库的情况下，序列会不会出现跳号，我们采用10046跟踪事件，看看正常数据库关闭情况下，会对序列做一些啥操作

SQL> select test.my_sequence.nextval from dual;NEXTVAL----------17085SQL> alter session set events '10046 trace name context forever, level 4';Session altered.SQL> shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.SQL> startupORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instanceORACLE instance started.Total System Global Area 1620115456 bytesFixed Size                  2213816 bytesVariable Size            1258293320 bytesDatabase Buffers          352321536 bytesRedo Buffers                7286784 bytesDatabase mounted.Database opened.SQL> select test.my_sequence.currval from dual;select test.my_sequence.currval from dual*ERROR at line 1:ORA-08002: sequence MY_SEQUENCE.CURRVAL is not yet defined in this sessionSQL> select test.my_sequence.nextval from dual;NEXTVAL----------17086SQL>

Trace文件中有更新seq$数据字典表，如果你看过我这篇文章ORACLE中seq$表更新频繁的分析，基本上就知道其实seq$中维护的是序列的一些信息。通过跟踪文件，我们知道在数据库正常关闭的情况下,会触发一个update seq$的操作,把当前的sequence.nextval的值更新到seq$.highwater中,从而使得sequence在有cache的情况下，数据库正常关闭未出现nextval跳跃(currval也同样不跳跃)；而在数据库异常关闭之时，数据库不能及时将sequence.nextval更新到eq$.highwater从而引起sequence cache中的值丢失，从而可能出现了sequence使用cache导致跳跃的情况

=====================PARSING IN CURSOR #25 len=129 dep=1 uid=0 oct=6 lid=0 tim=1504236336294194 hv=2635489469 ad='bf780410' sqlid='4m7m0t6fjcs5x'update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,order$=:6,cache=:7,highwater=:8,audit$=:9,flags=:10 where obj#=:1END OF STMTPARSE #25:c=0,e=21,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=1935744642,tim=1504236336294194BINDS #25:Bind#0oacdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00oacflg=18 fl2=0001 frm=00 csi=00 siz=24 off=0kxsbbbfp=bf45ca48  bln=24  avl=02  flg=09value=1Bind#1oacdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00oacflg=18 fl2=0001 frm=00 csi=00 siz=24 off=0kxsbbbfp=bf45ca5a  bln=24  avl=02  flg=09:/17086Bind#3oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0kxsbbbfp=2b7d80f57350  bln=24  avl=01  flg=05value=0Bind#4oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0kxsbbbfp=2b7d80f57320  bln=24  avl=01  flg=05value=0Bind#5oacdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00oacflg=18 fl2=0001 frm=00 csi=00 siz=24 off=0kxsbbbfp=bf47b85e  bln=24  avl=02  flg=09value=40Bind#6oacdty=02 mxl=22(04) mxlc=00 mal=00 scl=00 pre=00oacflg=18 fl2=0001 frm=00 csi=00 siz=24 off=0kxsbbbfp=bf47b870  bln=24  avl=04  flg=09value=17086Bind#7oacdty=01 mxl=32(32) mxlc=00 mal=00 scl=00 pre=00oacflg=18 fl2=0001 frm=01 csi=873 siz=32 off=0kxsbbbfp=bf47b882  bln=32  avl=32  flg=09value="--------------------------------"Bind#8oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0kxsbbbfp=2b7d80f572f0  bln=24  avl=02  flg=05value=8Bind#9oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0kxsbbbfp=2b7d80f57380  bln=22  avl=04  flg=05value=97760EXEC #9:c=999,e=709,p=0,cr=1,cu=2,mis=0,r=1,dep=1,og=4,plh=1935744642,tim=1504236336297033CLOSE #9:c=0,e=2,dep=1,type=3,tim=1504236336297058

mealink上提到了使用dbms_shared_pool.keep将对象在锁定在shared pool 中，永远不释放。这样可以防止FLUSH SHARED POOL导致序列跳号，但是这个无法避免数据库异常关闭或CRASH引起的跳号

SQL> select test.my_sequence.currval from dual;CURRVAL----------17086SQL> exec dbms_shared_pool.keep('test.my_sequence','q');PL/SQL procedure successfully completed.SQL> alter system flush shared_pool;System altered.SQL> select test.my_sequence.currval from dual;CURRVAL----------17086SQL> shutdown abortORACLE instance shut down.SQL> startupORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instanceORACLE instance started.Total System Global Area 1620115456 bytesFixed Size                  2213816 bytesVariable Size            1258293320 bytesDatabase Buffers          352321536 bytesRedo Buffers                7286784 bytesDatabase mounted.Database opened.SQL> select test.my_sequence.nextval from dual;NEXTVAL----------17126

其实如果业务允许，单号出现跳号也无所谓的情形最好，如果碰到业务要求绝对不能出现单号出现跳号的情况，那么就不能使用序列号了，就必须使用其它替代方案，此处不做展开说明！

参考资料：

Sequence Number Generator Generating Numbers With Gaps (文档 ID 470784.1)

Using the Oracle DBMS_SHARED_POOL Package (文档 ID 61760.1)

http://www.xifenfei.com/2015/04/%E6%AD%A3%E5%B8%B8%E5%85%B3%E9%97%AD%E6%95%B0%E6%8D%AE%E5%BA%93sequence-cache%E4%B8%8D%E4%B8%BA0-sequence%E4%B8%8D%E8%B7%B3%E8%B7%83.html

内容来源于网络如有侵权请私信删除