Re: User Sequence Skipping Numbers???

That you have set order to Y and removed caching helps to a large degree. Are your transactions performing any rollbacks? Sequence values once given out never get used again, even if the requesting transaction does a rollback. The same thing happens if Oracle crashes before the transaction has a change to commit. There's good reason for this behaviour: A requests a sequence value; then B requests a sequence value; A rollsback; how should Oracle notify B that its sequence value needs to be decremented to A's value so that there are no skips? Conversely, if Oracle wants to make sure that A commits, the when B requests a sequence value, B would have to block until A commits or rollsback so that Oracle would know whether to give B a new sequence value, or recycle the value that A threw away in the rollback. Oracle designed sequences to be non-blocking at the expense of losing values during rollbacks or database shutdowns if sequences are cached.

...Ru

Kathy Graham wrote in message
>Hi Folks;
> I have set up a new production application that uses user
sequences
>for the work/job numbers. The groups that use these need to use all
of
>the job numbers and are very concerned when the job numbers jump/skip
>for no apparent reason. (As I am as well.) I can look at the user
>sequences and see that I have the order flag set to Y and I have
nocache
>set as well. (This seems to have lowered the number of numbers
skipped
>say from 20 to 2 or 3, but it is still a problem.) I am not sure
what
>else to fix or look at. I am running on Oracle 7.3.2.3.0. Any
ideas?
>Thanks in advance....
>
>
>Kathy Graham
>
>
Received on Wed Mar 31 1999 - 13:35:17 CST