On Fri, 29 Jan 1999, amit p wrote:
> Dear Ari,
>
> Are Oracle sequences perfect i.e do they generate unique numbers in
> perfect order all the time ??.In our system most of our primary keys are
> based on sequences and it looks like all numbers generated are unique
> but not necessarily in right order.It tends to skip them
> sometimes .Is it a bug in oracle sequences,does it have any patch?.
> The version we use is 7.3.3 on HP-UX 10.20
> Pls let me know If you now anything about it, I would be extremely
> grateful to you
>
> Thanks,
> Amit
Amit,
What you are most likely experiencing is the normal behavior of sequences.
In order to ensure that there is no chance that a sequence provides the
same number to different sessions, Oracle caches the sequence values and
distributes them to all referring sessions. So, if two users are inserting
records into the table and one issues a ROLLBACK, the records containing
the sequence numbers are not inserted into the table. This results in
"missing" numbers in your primary key.
This should not be a problem, as long as the primary key is unique it will
serve its purpose.
Best regards,
-Ari Kaplan
Independent Oracle DBA Consultant
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
<-> For 250+ Oracle tips, visit my Web Page: <->
<-> <->
<-> www.arikaplan.com <->
<-> <->
<-> email: akaplan@interaccess.com <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
Back to Ari Kaplan's Home Page