AI 助理
备案控制台
开发者社区 数据库 文章 正文

ORACLE SEQUENCE跳号总结

2019-06-01 2059
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介： 在ORACLE数据库中，序列（SEQUENCE）是使用非常频繁的一个数据库对象，但是有时候会遇到序列（SEQUECNE）跳号(skip sequence numbers)的情形，那么在哪些情形下会遇到跳号呢？     事务回滚引起的跳号     不管序列有没有CACHE、事务回滚这种情况下，都会引起序列的跳号。

 

    在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>

clip_image001

 

 

并发访问序列引起的跳号

 

 

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

 

会话窗口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;

 

如下所示，我构造的实验当中，你会看到序列的跳号情况。

 

 

clip_image002

 

 

 

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

 

 

clip_image003

 

 

数据库实例异常关闭导致跳号

 

 

如下实验所示，当数据库使用shutdown abort命令关闭后，重新启动实例，序列缓存在shared pool里面没有用过的值都没有了。一下子从17045跳到17085

 

SQL> select test.my_sequence.currval from dual;
 
   CURRVAL
----------
     17045
 
SQL> select object_id from dba_objects where object_name=upper('my_sequence');
 
 OBJECT_ID
----------
     97760
 
SQL> select increment$, minvalue, maxvalue,highwater, cache
  2  from seq$ where obj#=97760;
 
INCREMENT$   MINVALUE   MAXVALUE  HIGHWATER      CACHE
---------- ---------- ---------- ---------- ----------
         1          1      99999      17085         40
 
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
 
Total System Global Area 1620115456 bytes
Fixed Size                  2213816 bytes
Variable Size            1258293320 bytes
Database Buffers          352321536 bytes
Redo Buffers                7286784 bytes
Database 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 session
 
SQL> select test.my_sequence.nextval from dual;
 
   NEXTVAL
----------
     17085
 
SQL>

 

 

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

 

 

SQL> select test.my_sequence.nextval from dual;
 
   NEXTVAL
----------
     17085
 
SQL> 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> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
 
Total System Global Area 1620115456 bytes
Fixed Size                  2213816 bytes
Variable Size            1258293320 bytes
Database Buffers          352321536 bytes
Redo Buffers                7286784 bytes
Database 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 session
 
 
SQL> select test.my_sequence.nextval from dual;
 
   NEXTVAL
----------
     17086
 
SQL>

 

 

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#=:1
END OF STMT
PARSE #25:c=0,e=21,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=1935744642,tim=1504236336294194
BINDS #25:
 Bind#0
  oacdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00
  oacflg=18 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=bf45ca48  bln=24  avl=02  flg=09
  value=1
 Bind#1
  oacdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00
  oacflg=18 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=bf45ca5a  bln=24  avl=02  flg=09
:/17086                                                                                                                                                                     
 Bind#3
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=2b7d80f57350  bln=24  avl=01  flg=05
  value=0
 Bind#4
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=2b7d80f57320  bln=24  avl=01  flg=05
  value=0
 Bind#5
  oacdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00
  oacflg=18 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=bf47b85e  bln=24  avl=02  flg=09
  value=40
 Bind#6
  oacdty=02 mxl=22(04) mxlc=00 mal=00 scl=00 pre=00
  oacflg=18 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=bf47b870  bln=24  avl=04  flg=09
  value=17086
 Bind#7
  oacdty=01 mxl=32(32) mxlc=00 mal=00 scl=00 pre=00
  oacflg=18 fl2=0001 frm=01 csi=873 siz=32 off=0
  kxsbbbfp=bf47b882  bln=32  avl=32  flg=09
  value="--------------------------------"
 Bind#8
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=2b7d80f572f0  bln=24  avl=02  flg=05
  value=8
 Bind#9
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=2b7d80f57380  bln=22  avl=04  flg=05
  value=97760
EXEC #9:c=999,e=709,p=0,cr=1,cu=2,mis=0,r=1,dep=1,og=4,plh=1935744642,tim=1504236336297033
CLOSE #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
----------
     17086
 
SQL> 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
----------
     17086
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
 
Total System Global Area 1620115456 bytes
Fixed Size                  2213816 bytes
Variable Size            1258293320 bytes
Database Buffers          352321536 bytes
Redo Buffers                7286784 bytes
Database mounted.
Database opened.
SQL> select test.my_sequence.nextval from dual;
   NEXTVAL
----------
     17126

 

clip_image004

 

 

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

 

 

 

参考资料：

clip_image006

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

 

文章标签：
关系型数据库
数据库
SQL
Oracle
关键词：
Oracle数据库一键上云sequence
Oracle数据库一键上云总结
潇湘隐者
目录
相关文章
恬0917
|
7月前
|
SQL Oracle 关系型数据库
整合Mybatis-Plus高级，Oracle 主键Sequence，Sql 注入器实现自定义全局操作
整合Mybatis-Plus高级，Oracle 主键Sequence，Sql 注入器实现自定义全局操作
恬0917
150 0
JamieChyi
|
缓存 Oracle 关系型数据库
使用序列（Sequence）在 Oracle 数据库中管理唯一标识符
使用序列（Sequence）在 Oracle 数据库中管理唯一标识符
JamieChyi
214 0
技术小结
|
缓存 Oracle 关系型数据库
Sequence在Oracle中的使用
Sequence在Oracle中的使用
技术小结
108 0
已经济建设为中心
|
SQL XML Oracle
整合Mybatis-Plus高级，Oracle 主键Sequence，Sql 注入器实现自定义全局操作（下）
整合Mybatis-Plus高级，Oracle 主键Sequence，Sql 注入器实现自定义全局操作（下）
已经济建设为中心
384 0
已经济建设为中心
|
SQL XML Oracle
整合Mybatis-Plus高级，Oracle 主键Sequence，Sql 注入器实现自定义全局操作（中）
整合Mybatis-Plus高级，Oracle 主键Sequence，Sql 注入器实现自定义全局操作（中）
已经济建设为中心
177 0
已经济建设为中心
|
SQL Oracle 关系型数据库
整合Mybatis-Plus高级，Oracle 主键Sequence，Sql 注入器实现自定义全局操作（上）
整合Mybatis-Plus高级，Oracle 主键Sequence，Sql 注入器实现自定义全局操作（上）
已经济建设为中心
482 0
游客tmj4hgt7bfvas
|
Oracle 关系型数据库 BI
Oracle-分析函数_总结
Oracle-分析函数_总结
游客tmj4hgt7bfvas
335 0
java3y
|
SQL Oracle 算法
Oracle总结【SQL细节、多表查询、分组查询、分页】下
在之前已经大概了解过Mysql数据库和学过相关的Oracle知识点，但是太久没用过Oracle了，就基本忘了…印象中就只有基本的SQL语句和相关一些概念….写下本博文的原因就是记载着Oracle一些以前没注意到的知识点…以后或许会有用… 实例与数据库概念
java3y
303 0
Oracle总结【SQL细节、多表查询、分组查询、分页】下
java3y
|
SQL Oracle 关系型数据库
Oracle总结【SQL细节、多表查询、分组查询、分页】上
在之前已经大概了解过Mysql数据库和学过相关的Oracle知识点，但是太久没用过Oracle了，就基本忘了…印象中就只有基本的SQL语句和相关一些概念….写下本博文的原因就是记载着Oracle一些以前没注意到的知识点…以后或许会有用… 实例与数据库概念
java3y
152 0
Oracle总结【SQL细节、多表查询、分组查询、分页】上
java3y
|
SQL 存储 Oracle
Oracle总结【视图、索引、事务、用户权限、批量操作】三
在Oracle总结的第一篇中，我们已经总结了一些常用的SQL相关的知识点了…那么本篇主要总结关于Oralce视图、序列、事务的一些内容… 在数据库中，我们可以把各种的SQL语句分为四大类… （1）DML（数据操纵语言）：select，insert，update，delete （2）DDL（数据定义语言）：create table，alter table，drop table，truncate table （3）DCL（数据控制语言）：grant select any table to scott/revoke select any table from scott （4）TCL（事务
java3y
165 0
Oracle总结【视图、索引、事务、用户权限、批量操作】三