oralce正常关闭数据库sequence cache不为0 sequence不跳跃
在Oracle中sequence使用很频繁，而大部分使用都配合了cache一起使用,那在sequence+cache一起使用的过程中,如果数据库正常关闭sequence.nextval如果变化,如果数据库异常关闭sequence.nextval又如何变化？这里通过试验进行了证明,结论为：在有cache的sequence中,正常关闭数据库sequence不会出现跳跃,异常关闭数据库很可能导致sequence出现跳跃
创建sequence测试
[oracle@localhost ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sun Apr 5 15:44:23 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select sysdate "www.111com.net" from dual;
www.xifen
---------
05-APR-15
SQL> create sequence seq_xifenfei
2 minvalue 1
3 maxvalue 100000
4 start with 1
5 increment by 1
6 cache 100;
Sequence created.
SQL> select SEQ_XIFENFEI.nextval from dual;
NEXTVAL
----------
1
SQL> /
NEXTVAL
----------
2
SQL> /
NEXTVAL
----------
3
SQL> select object_id from dba_objects where object_name='SEQ_XIFENFEI';
OBJECT_ID
----------
87549
SQL> SELECT MINVALUE,HIGHWATER,INCREMENT$, CYCLE#,ORDER$,CACHE from seq$ where obj#=87549;
MINVALUE HIGHWATER INCREMENT$ CYCLE# ORDER$ CACHE
---------- ---------- ---------- ---------- ---------- ----------
1 101 1 0 0 100
这里创建了一个名为SEQ_XIFENFEI的sequence,并且nextval已经查询到3,而且cache值设置为100,object_id为87549.
正常关闭数据库测试sequence.nextval变化
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1570009088 bytes
Fixed Size 2253584 bytes
Variable Size 469765360 bytes
Database Buffers 1090519040 bytes
Redo Buffers 7471104 bytes
Database mounted.
Database opened.
SQL> SELECT MINVALUE,HIGHWATER,INCREMENT$, CYCLE#,ORDER$,CACHE from seq$ where obj#=87549;
MINVALUE HIGHWATER INCREMENT$ CYCLE# ORDER$ CACHE
---------- ---------- ---------- ---------- ---------- ----------
1 4 1 0 0 100
SQL> select SEQ_XIFENFEI.nextval from dual;
NEXTVAL
----------
4
SQL> SELECT MINVALUE,HIGHWATER,INCREMENT$, CYCLE#,ORDER$,CACHE from seq$ where obj#=87549;
MINVALUE HIGHWATER INCREMENT$ CYCLE# ORDER$ CACHE
---------- ---------- ---------- ---------- ---------- ----------
1 104 1 0 0 100
正常关闭数据库启动后,sequence.nextval依然在上次基础之上增加，并为出现跳跃现象.
异常关闭数据库测试sequence.nextval变化
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1570009088 bytes
Fixed Size 2253584 bytes
Variable Size 469765360 bytes
Database Buffers 1090519040 bytes
Redo Buffers 7471104 bytes
Database mounted.
Database opened.
SQL> SELECT MINVALUE,HIGHWATER,INCREMENT$, CYCLE#,ORDER$,CACHE from seq$ where obj#=87549;
MINVALUE HIGHWATER INCREMENT$ CYCLE# ORDER$ CACHE
---------- ---------- ---------- ---------- ---------- ----------
1 104 1 0 0 100
SQL> select SEQ_XIFENFEI.nextval from dual;
NEXTVAL
----------
104
SQL> SELECT MINVALUE,HIGHWATER,INCREMENT$, CYCLE#,ORDER$,CACHE from seq$ where obj#=87549;
MINVALUE HIGHWATER INCREMENT$ CYCLE# ORDER$ CACHE
---------- ---------- ---------- ---------- ---------- ----------
1 204 1 0 0 100
异常关闭数据库启动后,sequence.nextvla发生跳跃从本该5直接跳跃到了104.
跟踪数据库正常关闭过程
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
ORACLE instance started.
Total System Global Area 1570009088 bytes
Fixed Size 2253584 bytes
Variable Size 469765360 bytes
Database Buffers 1090519040 bytes
Redo Buffers 7471104 bytes
Database mounted.
Database opened.
SQL> SELECT MINVALUE,HIGHWATER,INCREMENT$, CYCLE#,ORDER$,CACHE from seq$ where obj#=87549;
MINVALUE HIGHWATER INCREMENT$ CYCLE# ORDER$ CACHE
---------- ---------- ---------- ---------- ---------- ----------
1 105 1 0 0 100
SQL> select SEQ_XIFENFEI.nextval from dual;
NEXTVAL
----------
105
SQL> SELECT MINVALUE,HIGHWATER,INCREMENT$, CYCLE#,ORDER$,CACHE from seq$ where obj#=87549;
MINVALUE HIGHWATER INCREMENT$ CYCLE# ORDER$ CACHE
---------- ---------- ---------- ---------- ---------- ----------
1 205 1 0 0 100
再次证明了正常关闭数据库后,sequence.nextval未发生跳跃.
分析trace文件
PARSING IN CURSOR #139819144537744 len=129 dep=1 uid=0 oct=6 lid=0 tim=1428220381105111
hv=2635489469 ad='bc6e6c30' 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
BINDS #139819144537744:
Bind#0
oacdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=bc41e288 bln=22 avl=02 flg=09
value=1
Bind#1
oacdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=bc41e29a bln=22 avl=02 flg=09
value=1
Bind#2
oacdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=bc41e2ac bln=22 avl=02 flg=09
value=100000
Bind#3
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=48 off=0
kxsbbbfp=7f2a2edc2da8 bln=22 avl=01 flg=05
value=0
Bind#4
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=24
kxsbbbfp=7f2a2edc2dc0 bln=22 avl=01 flg=01
value=0
Bind#5
oacdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=bc41e2be bln=22 avl=02 flg=09
value=100
Bind#6
oacdty=02 mxl=22(03) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=bc41e2d0 bln=22 avl=03 flg=09
value=105 < ----SEQ_XIFENFEI.nextval值
Bind#7
oacdty=01 mxl=32(32) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=01 csi=852 siz=32 off=0
kxsbbbfp=bc41e2e2 bln=32 avl=32 flg=09
value="--------------------------------"
Bind#8
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=48 off=0
kxsbbbfp=7f2a2edc2d60 bln=22 avl=01 flg=05
value=0
Bind#9
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=24
kxsbbbfp=7f2a2edc2d78 bln=22 avl=04 flg=01
value=87549 <---这个就是我们的sequence(SEQ_XIFENFEI)
EXEC #139819144537744:c=0,e=650,p=0,cr=1,cu=2,mis=0,r=1,dep=1,og=4,plh=1935744642,tim=1428220381108066
CLOSE #139819144537744:c=0,e=2,dep=1,type=3,tim=1428220381108119
这里我们找出来了为什么数据库正常关闭sequence.nextval在有cache的情况下,未发生跳跃:因为在数据库正常关闭的情况下,会触发一个update seq$的操作,把当前的sequence.nextval的值更新到seq$.highwater中,从而使得sequence在有cache的情况下，数据库正常关闭未出现nextval跳跃(currval也同样不跳跃)；而在数据库异常关闭之时，数据库不能及时将sequence.nextval更新到eq$.highwater从而引起sequence cache中的值丢失，从而可能出现了sequence使用cache导致跳跃的情况
