在ORACLE數據庫中，序列（SEQUENCE）是使用非常頻繁的一個數據庫對象，但是有時候會遇到序列（SEQUECNE）跳號(skip sequence numbers)的情形，那么在哪些情形下會遇到跳號呢？
事務回滾引起的跳號
不管序列有沒有CACHE、事務回滾這種情況下，都會引起序列的跳號。如下實驗所示：
|
1
|
|
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
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> |
并發訪問序列引起的跳號
并發訪問序列引起的跳號，其實不算真正的跳號，而只是邏輯跳號，只是序列值被其它并發會話使用了。我們來構造一起并發訪問序列引起的跳號，我們開啟兩個會話窗口，循環獲取序列的值，模擬并發出現的場景。
會話窗口A：
|
1
|
|
2
3
4
5
6
7
8
|
exec dbms_lock.sleep(2); --延遲2秒執行，根據你實驗情況調整/begin for i in 1 .. 2000 loop dbms_output.put_line(my_sequence.nextval); end loop;end;/ |
會話窗口B：
|
1
|
|
2
3
4
5
6
7
8
9
|
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），當然正常情況下，很難遇到這種情況。
|
1
|
|
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
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 |
數據庫實例異常關閉導致跳號
如下實驗所示，當數據庫使用shutdown abort命令關閉后，重新啟動實例，序列緩存在shared pool里面沒有用過的值都沒有了。一下子從17045跳到17085
|
1
|
|
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
|
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> |
另外，我們也來看看正常關閉數據庫的情況下，序列會不會出現跳號，我們采用10046跟蹤事件，看看正常數據庫關閉情況下，會對序列做一些啥操作
|
1
|
|
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
|
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$數據字典表，如果你看過我這篇文章ORACLE中seq$表更新頻繁的分析，基本上就知道其實seq$中維護的是序列的一些信息。通過跟蹤文件，我們知道在數據庫正常關閉的情況下,會觸發一個update seq$的操作,把當前的sequence.nextval的值更新到seq$.highwater中,從而使得sequence在有cache的情況下，數據庫正常關閉未出現nextval跳躍(currval也同樣不跳躍)；而在數據庫異常關閉之時，數據庫不能及時將sequence.nextval更新到eq$.highwater從而引起sequence cache中的值丟失，從而可能出現了sequence使用cache導致跳躍的情況
|
1
|
|
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
|
=====================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將對象在鎖定在shared pool 中，永遠不釋放。這樣可以防止FLUSH SHARED POOL導致序列跳號，但是這個無法避免數據庫異常關閉或CRASH引起的跳號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 |
其實如果業務允許，單號出現跳號也無所謂的情形最好，如果碰到業務要求絕對不能出現單號出現跳號的情況，那么就不能使用序列號了，就必須使用其它替代方案，此處不做展開說明！
總結
以上所述是小編給大家介紹的分享ORACLE SEQUENCE跳號總結，希望對大家有所幫助，如果大家有任何疑問請給我留言，小編會及時回復大家的。在此也非常感謝大家對服務器之家網站的支持！
原文鏈接：http://www.cnblogs.com/kerrycode/archive/2017/09/01/7461180.html
