关于序列的号码丢失
首先看一个例子,让我们对CACHE丢失有一个直观的了解:
Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit ProductionWith 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> startupORACLE instance started.
Total System Global Area 1670221824 bytesFixed Size 2176328 bytesVariable Size 1073744568 bytesDatabase Buffers 587202560 bytesRedo Buffers 7098368 bytesDatabase mounted.Database opened.SQL> select seq_czw.nextval from dual;
NEXTVAL---------- 201
SQL> select seq_czw.nextval from dual;
NEXTVAL---------- 202
SQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL> startupORACLE instance started.
Total System Global Area 1670221824 bytesFixed Size 2176328 bytesVariable Size 1073744568 bytesDatabase Buffers 587202560 bytesRedo Buffers 7098368 bytesDatabase 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;
