分享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;
 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

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

总结

以上所述是小编给大家介绍的分享ORACLE SEQUENCE跳号总结,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对我们网站的支持!

(0)

相关推荐

  • oracle sequence语句重置方介绍

    在开发过程中,可能会用到oracle sequence语句,本文以oracle sequence语句如何重置进行介绍,需要的朋友可以参考下Oracle重置sequence语句1 Sql代码 复制代码 代码如下: DECLARE n NUMBER(10 ); tsql VARCHAR2(100 ); p_seqName varchar2(20 ); BEGIN p_seqName := 'SEQ_RUN_ID'; EXECUTE IMMEDIATE 'SELECT ' || p_seqName |

  • Oracle创建自增字段--ORACLE SEQUENCE的简单使用介绍

    先假设有这么一个表: 复制代码 代码如下: create table S_Depart ( DepartId INT not null, DepartName NVARCHAR2(40) not null, DepartOrder INT default 0, constraint PK_S_DEPART primary key (DepartId) ); 在oracle中sequence就是所谓的序列号,每次取的时候它会自动增加,一般用在需要按序列号排序的地方. 1.Create Sequen

  • Oracle中使用触发器(trigger)和序列(sequence)模拟实现自增列实例

    问题:在SQL Server数据库中,有自增列这个字段属性,使用起来也是很方便的.而在Oracle中却没有这个功能,该如何实现呢? 答:在Oracle中虽然没有自增列的说法,但却可以通过触发器(trigger)和序列(sequence)来模式实现. 示例: 1.建立表 复制代码 代码如下: create table user  (       id   number(6) not null,       name   varchar2(30)   not null primary key  )

  • sqlserver实现oracle的sequence方法

    当然这点小问题是难不倒我们程序员的,"max+1啊",有人会说这样的方式.是的,这种方式实现起来也比较简单.当然你也许还会说,最Sql的方式是采用identity列,自增的方式去增加就ok了.但是这样的列如果要实现"YYYYMMDD"+Sequence值 (例如:2008072400001)的格式就不行了.还是老老实实用存储过程来取得一个Sequence值吧,使用起来也就随心所欲了. 网上常见的一个存储过程为 为当前库所有用到Sequence建一张表,譬如"

  • 详解ORACLE SEQUENCE用法

    在oracle中sequence就是序号,每次取的时候它会自动增加.sequence与表没有关系. 1.Create Sequence 首先要有CREATE SEQUENCE或者CREATE ANY SEQUENCE权限. 创建语句如下: CREATE SEQUENCE seqTest INCREMENT BY 1 -- 每次加几个 START WITH 1 -- 从1开始计数 NOMAXvalue -- 不设置最大值 NOCYCLE -- 一直累加,不循环 CACHE 10; --设置缓存ca

  • Hibernate Oracle sequence的使用技巧

    一.为表创建自增长自段有两种,一种是不同的表使用各自的Sequence,方法如下: 1.在Oracle sequence首先创建sequence create sequence seq_idminvalue 1start with 1increment by 1cache 20; 2.在你的hbm.xml中的配置 seq_id 这样再插入数据的时候,Hibernate会自动生成如下语句: hibernate: select seq_id.nextval from dual hibernate:

  • 分享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;

  • Oracle按身份证号得到省市、性别、年龄的示例代码

    1.通过身份证号查询所在省市 SELECT count(*) as total, case substr(t.CERTNO,0,2) when '11' then '北京市' when '12' then '天津市' when '13' then '河北省' when '14' then '山西省' when '15' then '内蒙古自治区' when '21' then '辽宁省' when '22' then '吉林省' when '23' then '黑龙江省' when '31' t

  • 探讨Oracle中的&号问题

    在Oracle中inset里面的内容如果中有'&'号,有可能会插入失败,下面介绍一下方法:1.在pl/sql中:&可以用&&代替2.在sql developer中:&可以用||chr(38)||代替

  • 分享Oracle 11G Client 客户端安装步骤(图文详解)

    Oracle 11G Client 客户端安装步骤,具体如下: 下载地址:http://www.jb51.net/database/167737.html 先将下载下来的ZIP文件解压,并运行setup.exe文件. 执行到第四步之后,出现错误,直接点全部忽略就可以了. 把上面的步骤执行完了,那么就该等待Oracle的安装了. 现在这个就搞定了 1.安装Oracle 11G Client后可以在开始菜单中找到 选择NETCA->本地网络服务名配置 选择添加本地网服务名配置 这里的服务名:指的是也

  • oracle创建删除用户示例分享(oracle删除用户命令及授权)

    1.Oracle用户新增 复制代码 代码如下: create user lisi identified by lisi; 注:create user是创建数据库用户,后面跟用户名称,identified by是设置用户密码 2.把"连接"数据库权限授给新增用户lisi 复制代码 代码如下: grant connect to lisi; 注:grant:Oracle数据库授权的关键字connect:Oracle数据库的一个默认角色,只有连接上数据库的权限 3.把scott用户的表授权给l

随机推荐