文檔課題：模擬數(shù)據(jù)庫(kù)序列間斷場(chǎng)景.

1、概念

Gaps insequence values can occur when:

a、Arollback occurs 應(yīng)用出現(xiàn)回滾，但序列不會(huì)回滾

b、Thesystem crashes

c、Asequence is used in another table

2、實(shí)際操作

2.1、系統(tǒng)crash

SQL>select sequence_name,increment_by,cache_size,last_number,scale_flag,extend_flagfrom user_sequences;

SEQUENCE_NAME INCREMENT_BY CACHE_SIZELAST_NUMBER S E

------------------------------------------ ---------- ----------- - -

DEPARTMENTS_SEQ 10 0 280 N N

DEPT_DEPTID_SEQ 10 0 340 Y Y

DEPT_DEPTID_SEQ1 10 0 300 N N

EMPLOYEES_SEQ 1 0 207 N N

LOCATIONS_SEQ 100 0 3300 N N

SEQ_DEPT_DEPTID 10 0 350 Y Y

SEQ_DEPT_DEPTID1 10 10 710 N N

7 rowsselected.

SQL>select seq_dept_deptid1.nextval from dual;

NEXTVAL

----------

620

[oracle@dbserver~]$ ps -ef|grep ora_smon

oracle 3925 1 0 10:56 ? 00:00:01 ora_smon_orclcdb

oracle 12144 8251 0 18:28 pts/1 00:00:00 grep --color=auto ora_smon

[oracle@dbserver~]$ kill -9 3925

[oracle@dbserver~]$ ps -ef |grep ora_smon

oracle 12240 1 0 18:28 ? 00:00:00 ora_smon_orclcdb

oracle 12348 8251 0 18:28 pts/1 00:00:00 grep --color=auto ora_smon

說(shuō)明：此處還能看到orclcdb進(jìn)程，是因?yàn)镚I自動(dòng)開(kāi)啟數(shù)據(jù)庫(kù)進(jìn)程.

SQL>conn / as sysdba

Connected.

SQL>alter pluggable database orclpdb open;

Pluggabledatabase altered.

SQL>conn ora1/ora1@orclpdb

Connected.

SQL>select seq_dept_deptid1.nextval from dual;

NEXTVAL

----------

710

說(shuō)明：值從620變更為710，所以數(shù)據(jù)庫(kù)一旦crash，內(nèi)存的數(shù)據(jù)就丟失.

2.2、rollback

SQL>create sequence id_seq start with 1;

Sequencecreated.

SQL>create table emp (id number default id_seq.nextval not null,

2 name varchar2(10));

SQL>insert into emp (name) values ('john');

1 rowcreated.

SQL>insert into emp(name) values('mark');

1 rowcreated.

SQL>commit;

Commitcomplete.

SQL>select * from emp;

ID NAME

------------------------------

1 john

2 mark

SQL>select id_seq.nextval from dual;

NEXTVAL

----------

3

SQL>insert into emp (name) values ('jack');

1 rowcreated.

SQL>select * from emp;

ID NAME

------------------------------

1 john

2 mark

4 jack

SQL>rollback;

Rollbackcomplete.

SQL> select* from emp;

ID NAME

------------------------------

1 john

2 mark

SQL>insert into emp (name) values('jessie');

1 rowcreated.

SQL>commit;

Commitcomplete.

SQL>select * from emp;

ID NAME

------------------------------

1 john

2 mark

5 jessie

結(jié)果：序列ID出現(xiàn)間斷.

2.3、anothertable

SQL>create table emp (id number default id_seq.nextval not null,

2 name varchar2(10));

SQL>insert into emp (name) values ('john');

SQL>insert into emp (name) values ('jack');

1 rowcreated.

SQL>insert into emp (name) values ('jessie');

1 rowcreated.

SQL>commit;

Commitcomplete.

SQL>select * from emp;

ID NAME

--------------------

1 jack

2 jessie

SQL>create table emp01 (empid number default id_seq.nextval not null,

2 name varchar2(20));

Tablecreated.

SQL>insert into emp01(name) values ('cherry');

1 rowcreated.

SQL>commit;

Commitcomplete.

SQL>select * from emp01;

EMPID NAME

------------------------------

3 cherry

SQL>insert into emp(name) values ('jeff');

1 rowcreated.

SQL>commit;

Commitcomplete.

SQL>select * from emp;

ID NAME

--------------------

1 jack

2 jessie

4 jeff

結(jié)論：表emp序列ID出現(xiàn)間斷