Re: Weird SEQUENCE Problem

On Mar 1, 10:40 am, "TJ" <tjgra..._at_yahoo.com> 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

TJ,

What is the cache size for the sequence? Oracle populates cache size worth of sequneces into the SGA at a time but these sequnces are not yet assigned. Toad is probably showing you the last_number from DBA_SEQUENCES. UT1 > desc dba_sequences

 Name                                      Null?    Type
 ----------------------------------------- --------
----------------------------
 SEQUENCE_OWNER                            NOT NULL VARCHAR2(30)
 SEQUENCE_NAME                             NOT NULL VARCHAR2(30)
 MIN_VALUE                                          NUMBER
 MAX_VALUE                                          NUMBER
 INCREMENT_BY                              NOT NULL NUMBER
 CYCLE_FLAG                                         VARCHAR2(1)
 ORDER_FLAG                                         VARCHAR2(1)
 CACHE_SIZE                                NOT NULL NUMBER
 LAST_NUMBER                               NOT NULL NUMBER

But again the last number allocated is not the same as last number used.

You would be better off in the long run to use SQLPlus and learn how to query Oracle for the information you need than to use a GUI.

HTH -- Mark D Powell -- Received on Thu Mar 01 2007 - 09:47:32 CST