Oracle 序列上的 LAST_NUMBER
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23631628/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
LAST_NUMBER on oracle sequence
提问by being_uncertain
I have a sequence SEQ_PAGE_ID
我有一个序列 SEQ_PAGE_ID
SEQUENCE_NAME INCREMENT_BY CACHE_SIZE LAST_NUMBER
-------------------------------------------------------
SEQ_PAGE_ID 1 20 2222292456
To change the CACHE_SIZE, I used below script,
要更改 CACHE_SIZE，我使用了下面的脚本，
alter sequence SEQ_PAGE_ID CACHE 5000;
alter sequence SEQ_PAGE_ID CACHE 5000;
When I checked the query,
当我检查查询时，
select ... from user_sequences where sequence_name = 'SEQ_PAGE_ID';
SEQUENCE_NAME INCREMENT_BY CACHE_SIZE LAST_NUMBER
-------------------------------------------------------
SEQ_PAGE_ID 1 5000 2222292447
The LAST_NUMBERchanged from 2222292456to 2222292447.
Is this happened due to the alter script?
由LAST_NUMBER更改2222292456为2222292447。这是由于更改脚本而发生的吗？
回答by Alex Poole
This is normal, yes. From the documentation for the all_sequencesdata dictionary view, last_numberis:
这是正常的，是的。从数据字典视图的文档中all_sequences，last_number是：
Last sequence number written to disk. If a sequence uses caching, the number written to disk is the last number placed in the sequence cache. This number is likely to be greater than the last sequence number that was used.
写入磁盘的最后一个序列号。如果序列使用缓存，则写入磁盘的数字是放置在序列缓存中的最后一个数字。这个数字很可能大于上次使用的序列号。
This can be recreated with a fresh sequence:
这可以用一个新的序列重新创建：
SQL> create sequence SEQ_PAGE_ID start with 2222292436 increment by 1 cache 20;
sequence SEQ_PAGE_ID created.
SQL> select sequence_name, increment_by, cache_size, last_number
2 from user_sequences where sequence_name = 'SEQ_PAGE_ID';
SEQUENCE_NAME INCREMENT_BY CACHE_SIZE LAST_NUMBER
------------------------------ ------------ ---------- -----------
SEQ_PAGE_ID 1 20 2222292436
SQL> select SEQ_PAGE_ID.nextval from dual;
NEXTVAL
----------
2222292436
SQL> select sequence_name, increment_by, cache_size, last_number
2 from user_sequences where sequence_name = 'SEQ_PAGE_ID';
SEQUENCE_NAME INCREMENT_BY CACHE_SIZE LAST_NUMBER
------------------------------ ------------ ---------- -----------
SEQ_PAGE_ID 1 20 2222292456
The last_numberjumped up by the cache size, which is normal.
在last_number通过高速缓存的大小，这是正常的跳了起来。
SQL> alter sequence SEQ_PAGE_ID CACHE 5000;
sequence SEQ_PAGE_ID altered.
SQL> select sequence_name, increment_by, cache_size, last_number
2 from user_sequences where sequence_name = 'SEQ_PAGE_ID';
SEQUENCE_NAME INCREMENT_BY CACHE_SIZE LAST_NUMBER
------------------------------ ------------ ---------- -----------
SEQ_PAGE_ID 1 5000 2222292437
The last_numbergoes down, but now reflects the actual last sequence number generated. The DDL has (apparently) caused the data written to disk to be updated to reflect what happens to be the current value, rather than the top of the cache - either the old 20-value cache or the new 5000-value cache. In your case you got 2222292447, which just means you were ten values further through the cache than I was when I ran the alter.
在last_number下降，但是现在反映实际产生的最后一个序列号。DDL（显然）导致写入磁盘的数据被更新以反映当前值，而不是缓存的顶部——旧的 20 值缓存或新的 5000 值缓存。在你的情况下，你得到了2222292447，这只是意味着你通过缓存比我运行alter.
The value saved to disk is largely there so that if the database crashes it knows where to pick up from. On restart the sequence will start generating numbers from the recorded last_number. During normal running it doesn't need to refer back to that, it just updates the value on disk when new values are cached. This prevents sequence numbers being reissued after a crash, without needing to do expensive (slow) locking to maintain the value in real time - which is what the cache is there to avoid, after all.
保存到磁盘的值大部分都在那里，因此如果数据库崩溃，它知道从哪里获取。重新启动时，序列将开始从记录的last_number. 在正常运行期间，它不需要回参考那个，它只是在缓存新值时更新磁盘上的值。这可以防止在崩溃后重新发出序列号，而无需进行昂贵（缓慢）的锁定来实时维护该值——毕竟，这是缓存要避免的。
There would only be a problem if the last_valuewas lower than an actual generated sequence, but that can't happen. (Well, unless the sequence is set to cycle).
只有当last_value低于实际生成的序列时才会出现问题，但这不可能发生。（好吧，除非序列设置为循环）。
SQL> select SEQ_PAGE_ID.nextval from dual;
NEXTVAL
----------
2222292437
The next sequence number generated follows on from the last one before the cache size change; it hasn't reused an old value as you might have been worried about from the dictionary value.
下一个生成的序列号跟在缓存大小改变之前的最后一个序列号之后；它没有重用旧值，因为您可能担心字典值。
SQL> select sequence_name, increment_by, cache_size, last_number
2 from user_sequences where sequence_name = 'SEQ_PAGE_ID';
SEQUENCE_NAME INCREMENT_BY CACHE_SIZE LAST_NUMBER
------------------------------ ------------ ---------- -----------
SEQ_PAGE_ID 1 5000 2222297437
The last_numbernow shows the previous stored value incremented by the cache size of 5000. What is in the data dictionary now won't change again until we've consumed all 5000 values form the cache, or something happens elsewhere that affects it - the database being bounced, the sequence being altered again, etc.
在last_number现在显示通过的5000高速缓存大小是什么在数据字典，现在不会再改变递增上次存储的值，直到我们已经消耗了所有5000个值形成的高速缓存，或有事别处影响它-数据库中弹跳，顺序再次改变，等等。
回答by eliatou
When the sequence is in cache, last_number represent the number keep by oracle. When not, it represents the last sequence use by Oracle. With your alter command, you change the settings of the sequence, so Oracle flush it's "sequence cache"
当序列在缓存中时，last_number 代表oracle 保存的编号。如果不是，则表示 Oracle 使用的最后一个序列。使用alter 命令，您可以更改序列的设置，因此Oracle 会刷新它的“序列缓存”
Here a simple example
这里有一个简单的例子
SQL> drop sequence test;
Sequence dropped
SQL> create sequence test cache 20;
Sequence created
SQL> select last_number from user_sequences where sequence_name='TEST';
LAST_NUMBER
-----------
1
SQL> select test.nextval from dual;
NEXTVAL
----------
1
SQL> select last_number from user_sequences where sequence_name='TEST';
LAST_NUMBER
-----------
21
SQL> alter sequence test CACHE 5000;
Sequence altered
SQL> select last_number from user_sequences where sequence_name='TEST';
LAST_NUMBER
-----------
2
SQL> select test.nextval from dual;
NEXTVAL
----------
2
SQL> select last_number from user_sequences where sequence_name='TEST';
LAST_NUMBER
-----------
5002
SQL>
