关于序列的号码丢失

首先看一个例子,让我们对CACHE丢失有一个直观的了解:

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create sequence seq_czw

  2  start with 1

  3  nomaxvalue

  4  cache 200

  5  /

Sequence created.

SQL> select seq_czw.nextval from dual;

   NEXTVAL

----------

         1

SQL>  shutdown abort;

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 1670221824 bytes

Fixed Size                  2176328 bytes

Variable Size            1073744568 bytes

Database Buffers          587202560 bytes

Redo Buffers                7098368 bytes

Database mounted.

Database opened.

SQL> select seq_czw.nextval from dual;

   NEXTVAL

----------

       201

SQL> select seq_czw.nextval from dual;

   NEXTVAL

----------

       202

SQL>  shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 1670221824 bytes

Fixed Size                  2176328 bytes

Variable Size            1073744568 bytes

Database Buffers          587202560 bytes

Redo Buffers                7098368 bytes

Database mounted.

Database opened.

SQL> select seq_czw.nextval from dual;

   NEXTVAL

----------

       203

SQL>

看一下上面的显示结果,我们可以得到一个结论:

如果是shutdown abort的时候,就会丢失cache的数据,如果是shutdown immediate的时候,就不会丢失cache的数据.

找一下官方文档中的相关资料,可以看到如下的描述:

When a sequence is read into the sequence cache, sequence values are generated and stored in a cache entry. These values can then be accessed quickly. The number of sequence values stored in the cache is determined by the CACHE parameter in the CREATE SEQUENCE statement. The default value for this parameter is 20.

This CREATE SEQUENCE statement creates the seq2 sequence so that 50 values of the sequence are stored in the SEQUENCE cache:

CREATE SEQUENCE seq2
    CACHE 50;

The first 50 values of seq2 can then be read from the cache. When the 51st value is accessed, the next 50 values will be read from disk.

Choosing a high value for CACHE lets you access more successive sequence numbers with fewer reads from disk to the sequence cache. However, if there is an instance failure, then all sequence values in the cache are lost. Cached sequence numbers also could be skipped after an export and import if transactions continue to access the sequence numbers while the export is running.

If you use the NOCACHE option in the CREATE SEQUENCE statement, then the values of the sequence are not stored in the sequence cache. In this case, every access to the sequence requires a disk read. Such disk reads slow access to the sequence. This CREATE SEQUENCE statement creates the SEQ3 sequence so that its values are never stored in the cache:

CREATE SEQUENCE seq3
    NOCACHE;

作者 陈字文（热衷于PM\ORACLE\JAVA等，欢迎同行交流)EMAIL:ziwen@163.com  QQ:409020100

上面的红色字体部分说明,数据库在非正常关闭,或者在shutdown abort的时候,以及在导入导出的时候就可能会对序列的值有影响.其实本身由于事务的回滚以及触发时机等情况,总会导致序列在最终页面的展示上出现不连续或者跳号等情况.如果要保证连号的话,可能需要其他的技术手段来保证了.