Shulou(Shulou.com)06/01 Report--

In ORACLE database, SEQUENCE is a database object that is used very frequently, but sometimes you encounter a SEQUECNE (skip sequence numbers), so in which cases do you encounter a jump sign?

Hopping caused by transaction rollback

Regardless of whether the sequence has CACHE or the transaction is rolled back, it will cause the sequence to jump. As shown in the following experiment:

SQL > create sequence my_sequence 2 start with 1 3 increment by 14 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 >

Jump sign caused by concurrent access sequence

In fact, the hop caused by the concurrent access sequence is not a real hop, but only a logical hop, but the sequence value is used by other concurrent sessions. Let's construct a hop sign caused by a concurrent access sequence. We open two session windows, cycle through the values of the sequence, and simulate concurrency scenarios.

Session window A:

Exec dbms_lock.sleep (2);-delay execution by 2 seconds, adjust / begin for i in 1. 2000 loop dbms_output.put_line (my_sequence.nextval); end loop;end;/

Session window B:

Spool test.txt;begin waitfor delay'00, for i in 1. 2000 loop dbms_output.put_line (my_sequence.nextval); end loop;end;/spool off

As shown below, in the experiment I constructed, you will see the jump sign of the sequence.

FLUSH SHARED_POOL will cause the sequence of CACHE to jump

The experimental test is shown below (the cache value of the sequence must be greater than 0). Of course, under normal circumstances, it is difficult to encounter this situation.

SQL > select test.my_sequence.nextval from dual; NEXTVAL- 17004SQL > alter sequence test.my_sequence cache 40th 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

Abnormal shutdown of database instance caused jumper

As shown in the following experiment, when the database is shut down with the shutdown abort command, restart the instance, and the unused values of the sequence cache in shared pool are gone. Jump from 17045 to 17085 at once

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- 11 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 >

In addition, let's take a look at whether a jump mark will appear in the sequence when the database is normally closed. We use 10046 to track events to see what actions will be done to the sequence in the case of normal database shutdown.

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 file has updated seq$ data dictionary table, if you read my article ORACLE seq$ table updated frequently analysis, basically know that seq$ is actually maintaining some of the sequence of information. Through the tracking file, we know that when the database is normally closed, a update seq$ operation will be triggered to update the current sequence.nextval value to seq$.highwater, so that when sequence has cache, the database shuts down normally without nextval jump (currval also does not jump) When the database shuts down abnormally, the database cannot update sequence.nextval to eq$.highwater in time, which causes the value in sequence cache to be lost, which may lead to the jump caused by the use of cache by sequence.

= 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 VOBLING 0GRAPHY 21MIXOLY 0MIZOWELER CRON0RAPHY CUBLING 0MIANG 0MIXING 0MIXING 0MIXING 0MIXINOMIXINOMIZOMIZOMIZOMIHY 0DIREDIZING OGREOTHY 4, plhages 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 EBay 709 journal crackers 1, cubes 2, memorials 0, memorials 1, magical1, magical1, magicalin1, magicalink, 5, 4, 4, 4, 5, 4, 4, 5, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 3, 4, 4, 3, 4, 4, 3, 4, 3, 4, 3, 4, 3, 4, 3, 4, 3, 4, 3, 4, 3, 4, 3, 4, 3, 4, 3, 4, 3, 4, 3, 4, 3, 4, 3, 4, 3, 4, 3, 4, 3, 4, 3, 4, 3, Never released. This prevents FLUSH SHARED POOL from causing sequence jumps, but this cannot avoid 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 caused by abnormal database shutdown or CRASH 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

In fact, if the business allows, it doesn't matter if the order number has a jump number. If you encounter the business requirements, you absolutely can't have a jump number, then you can't use the serial number, and you have to use other alternatives. No instructions here!

Summary

The above is the summary of sharing ORACLE SEQUENCE jump numbers introduced by the editor to you. I hope it will be helpful to you. If you have any questions, please leave a message for me, and the editor will reply you in time. Thank you very much for your support to the website!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.