Re: Weird SEQUENCE Problem

Right, by default 20 values are cached in memory when creating the sequence... This will lead to faster performance when referencing the sequence but can also cause gaps in your sequence value depending on what you are using it for (i.e. you have inserted 5 values, dba's take down db for new 07 daylight savings patch, you have now lost your cached values and it will load a 21-40 in memory and you will be sitting on 21). Consider adding the NOCACHE option when creating this sequence.

Regards,
Dave

On Mar 1, 1:49 pm, DA Morgan <damor..._at_psoug.org> wrote:
> TJ wrote:
> > I have a sequence that is defined correctly. According to TOAD, the
> > Last Value generated by the sequence is '21'.
>
> > However, when I execute:
>
> > SELECT MY_SEQUENCE.NEXTVAL FROM DUAL;
>
> > The value is '1'. If I do it again, then the value is '2'. Obviously,
> > I can continue to do this until I get the number I want, but why is
> > NEXTVAL returning me the wrong number?
>
> > One thing that may help... I believe that the sequence is resetting
> > to '1' when we bring the DB down and restart it. I haven't proven this
> > yet, but this has been a problem that recurs intermittently and that
> > is my theory at this point.
>
> > Thanks.
>
> > TJ
>
> Nothing weird. Oracle is doing exactly as documented.
>
> By default a sequence cache's 20 numbers. The next time the
> sequence generates numbers it will start with 21.
> --
> Daniel A. Morgan
> University of Washington
> damor..._at_x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org
Received on Sat Mar 03 2007 - 21:29:12 CST