在ORACLE數(shù)據(jù)庫(kù)中，序列（SEQUENCE）是使用非常頻繁的一個(gè)數(shù)據(jù)庫(kù)對(duì)象，但是有時(shí)候會(huì)遇到序列（SEQUECNE）跳號(hào)(skip sequence numbers)的情形，那么在哪些情形下會(huì)遇到跳號(hào)呢？
事務(wù)回滾引起的跳號(hào)
不管序列有沒(méi)有CACHE、事務(wù)回滾這種情況下，都會(huì)引起序列的跳號(hào)。如下實(shí)驗(yàn)所示：
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---------- 3SQL>
并發(fā)訪問(wèn)序列引起的跳號(hào)
并發(fā)訪問(wèn)序列引起的跳號(hào)，其實(shí)不算真正的跳號(hào)，而只是邏輯跳號(hào)，只是序列值被其它并發(fā)會(huì)話使用了。我們來(lái)構(gòu)造一起并發(fā)訪問(wèn)序列引起的跳號(hào)，我們開(kāi)啟兩個(gè)會(huì)話窗口，循環(huán)獲取序列的值，模擬并發(fā)出現(xiàn)的場(chǎng)景。
會(huì)話窗口A：
exec dbms_lock.sleep(2); --延遲2秒執(zhí)行，根據(jù)你實(shí)驗(yàn)情況調(diào)整/begin for i in 1 .. 2000 loop dbms_output.put_line(my_sequence.nextval); end loop;end;/
會(huì)話窗口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;
如下所示，我構(gòu)造的實(shí)驗(yàn)當(dāng)中，你會(huì)看到序列的跳號(hào)情況。
FLUSH SHARED_POOL會(huì)導(dǎo)致CACHE的序列跳號(hào)
實(shí)驗(yàn)測(cè)試如下所示（序列的CACHE值必須大于0），當(dāng)然正常情況下，很難遇到這種情況。
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
數(shù)據(jù)庫(kù)實(shí)例異常關(guān)閉導(dǎo)致跳號(hào)
如下實(shí)驗(yàn)所示，當(dāng)數(shù)據(jù)庫(kù)使用shutdown abort命令關(guān)閉后，重新啟動(dòng)實(shí)例，序列緩存在shared pool里面沒(méi)有用過(guò)的值都沒(méi)有了。一下子從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, cache 2 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> 另外，我們也來(lái)看看正常關(guān)閉數(shù)據(jù)庫(kù)的情況下，序列會(huì)不會(huì)出現(xiàn)跳號(hào)，我們采用10046跟蹤事件，看看正常數(shù)據(jù)庫(kù)關(guān)閉情況下，會(huì)對(duì)序列做一些啥操作
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$數(shù)據(jù)字典表，如果你看過(guò)我這篇文章ORACLE中seq$表更新頻繁的分析，基本上就知道其實(shí)seq$中維護(hù)的是序列的一些信息。通過(guò)跟蹤文件，我們知道在數(shù)據(jù)庫(kù)正常關(guān)閉的情況下,會(huì)觸發(fā)一個(gè)update seq$的操作,把當(dāng)前的sequence.nextval的值更新到seq$.highwater中,從而使得sequence在有cache的情況下，數(shù)據(jù)庫(kù)正常關(guān)閉未出現(xiàn)nextval跳躍(currval也同樣不跳躍)；而在數(shù)據(jù)庫(kù)異常關(guān)閉之時(shí)，數(shù)據(jù)庫(kù)不能及時(shí)將sequence.nextval更新到eq$.highwater從而引起sequence cache中的值丟失，從而可能出現(xiàn)了sequence使用cache導(dǎo)致跳躍的情況
=====================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#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=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=1504236336297058mealink上提到了使用dbms_shared_pool.keep將對(duì)象在鎖定在shared pool 中，永遠(yuǎn)不釋放。這樣可以防止FLUSH SHARED POOL導(dǎo)致序列跳號(hào)，但是這個(gè)無(wú)法避免數(shù)據(jù)庫(kù)異常關(guān)閉或CRASH引起的跳號(hào)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 
其實(shí)如果業(yè)務(wù)允許，單號(hào)出現(xiàn)跳號(hào)也無(wú)所謂的情形最好，如果碰到業(yè)務(wù)要求絕對(duì)不能出現(xiàn)單號(hào)出現(xiàn)跳號(hào)的情況，那么就不能使用序列號(hào)了，就必須使用其它替代方案，此處不做展開(kāi)說(shuō)明！
總結(jié)
以上所述是小編給大家介紹的分享ORACLE SEQUENCE跳號(hào)總結(jié)，希望對(duì)大家有所幫助，如果大家有任何疑問(wèn)請(qǐng)給我留言，小編會(huì)及時(shí)回復(fù)大家的。在此也非常感謝大家對(duì)VeVb武林網(wǎng)網(wǎng)站的支持！
新聞熱點(diǎn)
疑難解答
圖片精選
