文档课题：模拟数据库序列间断场景.
1、概念
Gaps in sequence values can occur when:
a、A rollback occurs  应用出现回滚，但序列不会回滚
b、The system crashes
c、A sequence is used in another table
2、实际操作
2.1、系统crash
SQL> select sequence_name,increment_by,cache_size,last_number,scale_flag,extend_flag from user_sequences;

SEQUENCE_NAME                  INCREMENT_BY CACHE_SIZE LAST_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 rows selected. 

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;

Pluggable database 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;

Sequence created.
SQL> create table emp (id number default id_seq.nextval not null,
  2                    name varchar2(10));
SQL> insert into emp (name) values ('john');

1 row created.

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

1 row created.

SQL> commit;

Commit complete.

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 row created.

SQL> select * from emp;

        ID NAME
---------- --------------------
         1 john
         2 mark
         4 jack

SQL> rollback;

Rollback complete.

SQL> select * from emp;

        ID NAME
---------- --------------------
         1 john
         2 mark

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

1 row created.

SQL> commit;

Commit complete.

SQL> select * from emp;

        ID NAME
---------- --------------------
         1 john
         2 mark
         5 jessie
结果：序列ID出现间断.
2.3、another table
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 row created.

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

1 row created.

SQL> commit;

Commit complete.

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));

Table created.

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

1 row created.

SQL> commit;

Commit complete.

SQL> select * from emp01;

     EMPID NAME
---------- --------------------
         3 cherry

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

1 row created.

SQL> commit;

Commit complete.

SQL> select * from emp;

        ID NAME
---------- ----------
         1 jack
         2 jessie
         4 jeff
结论：表emp序列ID出现间断.