关于oracle序列的LAST_NUMBER [英] LAST_NUMBER on oracle sequence
问题描述
我有一个序列SEQ_PAGE_ID
I have a sequence SEQ_PAGE_ID
SEQUENCE_NAME INCREMENT_BY CACHE_SIZE LAST_NUMBER
-------------------------------------------------------
SEQ_PAGE_ID 1 20 2222292456
要更改CACHE_SIZE，我在下面的脚本中使用了
To change the CACHE_SIZE, I used below script,
alter sequence SEQ_PAGE_ID CACHE 5000;
当我检查查询时，
select ... from user_sequences where sequence_name = 'SEQ_PAGE_ID';
SEQUENCE_NAME INCREMENT_BY CACHE_SIZE LAST_NUMBER
-------------------------------------------------------
SEQ_PAGE_ID 1 5000 2222292447
LAST_NUMBER从2222292456更改为2222292447.
这是由于alter脚本引起的吗?
The LAST_NUMBER changed from 2222292456 to 2222292447.
Is this happened due to the alter script?
推荐答案
这是正常的，是的.从all_sequences数据字典视图的文档中，last_number是:
This is normal, yes. From the documentation for the all_sequences data dictionary view, last_number is:
最后一个序列号写入磁盘.如果序列使用高速缓存，则写入磁盘的数字是序列高速缓存中放置的最后一个数字.该数字可能大于所使用的最后一个序列号.
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
last_number会因缓存大小而跳升，这是正常现象.
The last_number jumped up by the cache size, which is normal.
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
last_number下降了，但现在反映了实际生成的最后一个序列号. DDL(显然)已导致写入磁盘的数据被更新，以反映当前值，而不是高速缓存的顶部(旧的20值高速缓存或新的5000值高速缓存).在您的情况下，您得到了2222292447，这仅仅意味着您通过缓存比我运行alter时还高了十个值.
The last_number goes 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中生成数字.在正常运行期间，它不需要引用它，它只是在缓存新值时更新磁盘上的值.这样可以防止崩溃后重新发出序列号，而无需进行昂贵的(慢速)锁定来实时维护该值-毕竟，这是避免缓存的方法.
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_value低于实际生成的序列时才会出现问题，但这不会发生. (好吧，除非将序列设置为循环).
There would only be a problem if the last_value was lower than an actual generated sequence, but that can't happen. (Well, unless the sequence is set to cycle).
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
last_number现在显示以前存储的值，该值增加了缓存大小5000.现在，在我们使用完缓存中的所有5000个值或发生其他事情之前，数据字典中的内容将不再更改.影响它-数据库被反弹，序列被再次更改，等等.
The last_number now 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.
这篇关于关于oracle序列的LAST_NUMBER的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
