重庆思庄Oracle、Redhat认证学习论坛

标题: 数据库序列间断场景 [打印本页]

作者: denglj 时间: 2022-9-6 09:30
标题: 数据库序列间断场景

文档课题：模拟数据库序列间断场景.

1、概念

Gaps insequence values can occur when:

a、Arollback occurs 应用出现回滚，但序列不会回滚

b、Thesystem crashes

c、Asequence is used in another table

2、实际操作

2.1、系统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

说明：此处还能看到orclcdb进程，是因为GI自动开启数据库进程.

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

说明：值从620变更为710，所以数据库一旦crash，内存的数据就丢失.

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

----------

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

结果：序列ID出现间断.

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

结论：表emp序列ID出现间断.

欢迎光临重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/)