Re: Sequence numbers jumping
Date: 1997/09/28
Message-ID: <342e8abd.12001497_at_newshost>#1/1
On 28 Sep 1997 06:35:40 GMT, "Rafael Cayama" <prime_at_telcel.net.ve> wrote:
>
>This happens because you created the sequence by default, without specify
>the cache parameter. Oracle by default set this to 20.
>Each time you request a sequence number, the Rdbms caches 20, (to be used
>in the same session), i.e. if you use 4 sequence numbers and disconnect the
>session, you will lost the 16 remaining sequence numbers. If you process
>only one sequence number per request you will be always jumping by 20. Fix
>it using the command:
>
>SQL> alter sequence <seq_name> nocache;
>
>Rafael Cayama
No, thats not the way sequences work. The cache option affects the server, not a session. Cache 20 (the default) may cause upto 20 sequence numbers to be 'lost' when you shutdown the instance and restart it. Simply logging off and logging in again will not cause this behavior, consider:
SQL> create sequence my_seq cache 20;
Sequence created.
SQL> select my_seq.nextval, userenv('sessionid') from dual;
NEXTVAL USERENV('SESSIONID')
---------- --------------------
1 1439
SQL> disconnect;
Disconnected from Oracle7 Server Release 7.3.3.0.0 - Production Release
With the distributed, replication, parallel query and Spatial Data options
PL/SQL Release 2.3.3.0.0 - Production
SQL> connect scott/tiger
Connected.
SQL> select my_seq.nextval, userenv('sessionid') from dual;
NEXTVAL USERENV('SESSIONID')
---------- --------------------
2 1440
SQL> I logged off and created a new session (as attested to by the sessionid) and the sequence did not skip any values. Also, my session does not own the 20 sequences, sequence values will be interleaved across concurrent sessions. Try this, log in 2 times in different windows. insert seq.nextval into a temp table in one session and insert seq.nextvalu into that table in another session, then go back to the first and do it again and the second and so on. The numbers will be interleaved. Window 1 will get sequences 1, 3, 5, 7....; Window 2 will get 2, 4, 6, 8, .....
The skipping of 20 values is probably due to a database restart at some point, losing the cache values.
>
>Fenella Tan <fenella_at_voicenet.com> wrote in article
><342EF476.CB9D9919_at_voicenet.com>...
>> We're having problems with our sequence numbers. For some reason, they
>> seem to be jumping up by 20 instead of the specified increment by 1.
>> Any ideas on what may cause this ? We're using Oracle 7.3 on UNIX with
>> a Visual Basic front-end. The jumps happen intermittently. It'll work
>> (increment by 1) for a few records, and then it'll suddenly go up by 20
>> again. Appreciate any input.
>>
>> Fenella
>>
>>
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sun Sep 28 1997 - 00:00:00 CEST