分享ORACLE SEQUENCE跳號總結
更新時間：2017年09月01日 09:47:06 作者：瀟湘隱者
在ORACLE數(shù)據(jù)庫中，序列（SEQUENCE）是使用非常頻繁的一個數(shù)據(jù)庫對象，但是有時候會遇到序列（SEQUECNE）跳號(skip sequence numbers)的情形，那么在哪些情形下會遇到跳號呢，下面通過本文給大家詳解介紹，一起看看吧
在ORACLE數(shù)據(jù)庫中，序列（SEQUENCE）是使用非常頻繁的一個數(shù)據(jù)庫對象，但是有時候會遇到序列（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>
并發(fā)訪問序列引起的跳號
并發(fā)訪問序列引起的跳號，其實不算真正的跳號，而只是邏輯跳號，只是序列值被其它并發(fā)會話使用了。我們來構造一起并發(fā)訪問序列引起的跳號，我們開啟兩個會話窗口，循環(huán)獲取序列的值，模擬并發(fā)出現(xiàn)的場景。
會話窗口A：
exec dbms_lock.sleep(2); --延遲2秒執(zhí)行，根據(jù)你實驗情況調(diào)整 / 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
數(shù)據(jù)庫實例異常關閉導致跳號
如下實驗所示，當數(shù)據(jù)庫使用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>
另外，我們也來看看正常關閉數(shù)據(jù)庫的情況下，序列會不會出現(xiàn)跳號，我們采用10046跟蹤事件，看看正常數(shù)據(jù)庫關閉情況下，會對序列做一些啥操作
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$數(shù)據(jù)字典表，如果你看過我這篇文章ORACLE中seq$表更新頻繁的分析，基本上就知道其實seq$中維護的是序列的一些信息。通過跟蹤文件，我們知道在數(shù)據(jù)庫正常關閉的情況下,會觸發(fā)一個update seq$的操作,把當前的sequence.nextval的值更新到seq$.highwater中,從而使得sequence在有cache的情況下，數(shù)據(jù)庫正常關閉未出現(xiàn)nextval跳躍(currval也同樣不跳躍)；而在數(shù)據(jù)庫異常關閉之時，數(shù)據(jù)庫不能及時將sequence.nextval更新到eq$.highwater從而引起sequence cache中的值丟失，從而可能出現(xiàn)了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導致序列跳號，但是這個無法避免數(shù)據(jù)庫異常關閉或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
其實如果業(yè)務允許，單號出現(xiàn)跳號也無所謂的情形最好，如果碰到業(yè)務要求絕對不能出現(xiàn)單號出現(xiàn)跳號的情況，那么就不能使用序列號了，就必須使用其它替代方案，此處不做展開說明！
總結
以上所述是小編給大家介紹的分享ORACLE SEQUENCE跳號總結，希望對大家有所幫助，如果大家有任何疑問請給我留言，小編會及時回復大家的。在此也非常感謝大家對腳本之家網(wǎng)站的支持！
相關文章
這是某培訓機構的oracle跟mysql視頻教程，現(xiàn)在分享給大家，想要學習mysql和oracle的同學不要錯過哦2013-03-03
Oracle 遍歷游標的四種方式匯總（for、fetch、while、BULK COLLECT）
這篇文章主要介紹了Oracle 遍歷游標的四種方式匯總（for、fetch、while、BULK COLLECT），幫助大家更好的理解和使用Oracle數(shù)據(jù)庫，感興趣的朋友可以了解下2020-10-10
這篇文章主要介紹了Oracle 12c安裝方法及一些使用問題,需要的朋友可以參考下2016-09-09
本文介紹了通過Oracle10g發(fā)送Email的實例，該Procedure支持中文郵件、HTML代碼、多收件人。 ,需要的朋友可以參考下2014-01-01
Oracle數(shù)據(jù)庫實現(xiàn)遠程訪問方法
Oracle數(shù)據(jù)庫的遠程連接可以通過多種方式來實現(xiàn),下面這篇文章主要給大家介紹了關于Oracle數(shù)據(jù)庫實現(xiàn)遠程訪問的相關資料,文中通過實例代碼介紹的非常詳細,需要的朋友可以參考下2023-06-06
表函數(shù)可接受查詢語句或游標作為輸入?yún)?shù)，并可輸出多行數(shù)據(jù)。這篇文章主要介紹了Oracle 中 table 函數(shù)的應用淺析,需要的朋友可以參考下2016-12-12
Oracle 數(shù)據(jù)庫針對表主鍵列并發(fā)導致行級鎖簡單演示
本文簡單演示針對表主鍵并發(fā)導致的行級鎖，鎖的產(chǎn)生是因為并發(fā)。沒有并發(fā)，就沒有鎖。并發(fā)的產(chǎn)生是因為系統(tǒng)需要，系統(tǒng)需要是因為用戶需要，感興趣的你可以參考下哈，希望可以幫助到你2013-03-03
更改計算機名稱后,oracle不能正常啟動的相信有很多的朋友都有遇到過這種情況吧，接下來為大家介紹下詳細的解決方法感興趣的朋友可以參考下哈2013-04-04
深入淺析mybatis oracle BLOB類型字段保存與讀取
本文給大家淺析mybatis oracle blob類型字段的保存與讀取，blob字段是指二進制大對象，用來存儲大量文本數(shù)據(jù)。感興趣的朋友一起學習吧2015-10-10
解決Oracle刪除重復數(shù)據(jù)只留一條的方法詳解
本篇文章是對Oracle刪除重復數(shù)據(jù)只留一條的解決方法進行了詳細的分析介紹，需要的朋友參考下2013-05-05