RE: sequence #'s skipping

When you create a sequence with caching (the default) Oracle loads the next 20 sequence values into its cache. Once the last value is selected from the cache it will cache the next 20 values. If the cached values are not used before the database is restarted the next select will pick the next value from the sequence and the unused values have been skipped.

Gary, for your question about replication, instead of depending on sequences (which you cannot synchronize across databases with replication) we added a creator_id field to the primary key in the replicated tables to store location information. Each database has a unique creator_id value and you do not depend on part of the sequence to store location information.

Chris Boyd
Sapient Corporation

> -----Original Message-----
> From: Gary Melhaff [SMTP:melhafg_at_wdni.com]
> Posted At: Wednesday, December 17, 1997 11:46 AM
> Posted To: comp.databases.oracle.misc
> Conversation: sequence #'s skipping
> Subject: Re: sequence #'s skipping
>
> Related question...when doing replication, I need to use offsetting
> sequences (location 1 uses 11/21/31/41..., 2 uses 12/22/32/43...).
>
> Can I use caching and not loose my interval setting? Sounds like
> caching subjects the interval to being reset to start on next highest
> 10
> rounded. If this is true, this could really foul up a replicated
> table
> where the primary key must have offsetting numbers.
>
>
>
> Steve Lehrfeld wrote:
> >
> > we found that Oracle 7 skips sequence #'s after it has been reset.
 It
> > always rounds to the nearest twenty, i.e. 10001, 10002, <reset>,
 10020,
> > 10021, 10022, <reset>, 10040, etc.
> >
> > Does anyone know why it does this and how to prevent it?
> >
> > -steve
>
> --
> Gary Melhaff
> Senior Database Analyst
> Weyerhauser Corporation
Received on Wed Dec 17 1997 - 00:00:00 CST