| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: sequence count not correct
On Thu, 15 Jul 1999 14:12:46 +0200, Steffi <steffi_at_planet.de> wrote:
>hi everybody.
>
>In my tables I use as ID a sequence.
>
>If I insert (nr.nextval) it's ok...the sequence is 1 to 5. Yes it's ok
>
>But if I stop and start my database already then my sequence
>is 21 and not 6. hm..I don't understand this. Can anybody help me??
>
from the Server SQL Reference
<quote>
Caching Sequence Numbers
The number of values cached in memory for a sequence is specified by the
value of the sequence’s CACHE parameter. Cached sequences allow faster
generation of sequence numbers. A cache for a given sequence is populated at
the first request for a number from that sequence. The cache is repopulated
every CACHE requests. If there is a system failure, all cached sequence values
that have not been used in committed Data Manipulation Language
statements are lost. The potential number of lost values is equal to the value
of
the CACHE parameter.
A CACHE of 20 future sequence numbers is the default.
</quote>
So shutting down your database loses all the values cached. That is why the next number is 21. If you do not want to lose values due to shutdown then set the cache for the sequence to 1. This will slow down fetching of new sequences and does NOT guarantee contiguous numbers. Why? Well to quote the reference manual again...
<quote>
When a sequence number is generated, the sequence is incremented, independent of the transaction committing or rolling back. If two users concurrently increment the same sequence, the sequence numbers each user acquires may have gaps because sequence numbers are being generated by the other user. One user can never acquire the sequence number generated by another user. Once a sequence value is generated by one user, that user can continue to access that value regardless of whether the sequence is incremented by another user.
Because sequence numbers are generated independently of tables, the same sequence can be used for one or for multiple tables. It is possible that individual sequence numbers will appear to be skipped, because they were generated and used in a transaction that ultimately rolled back. Additionally, a single user may not realize that other users are drawing from the same sequence.
</quote>
hope this helps.
chris.
>Greetings Steffi
--
Christopher Beck
Oracle Corporation
clbeck_at_us.oracle.com
Reston, VA.
 |
 |