Re: Sequence numbers jumping

sequence_cache_entries must be set in your parameter file large enough (at least the total number of distinct sequence numbers.) And you need to have enough shared_pool_size to also accomodate. In Oracle V7.3 we've gone to 9000000 minimum w/ 300 sequence numbers and then generally all of our numbers seem to stay cached.

(What is happening is that Oracle does not have enough memory to keep all of your sequence numbers cached, so when one gets bumped out, you lose all the numbers that have been cached, typically the next 19, and then when you use it again, it gets cached w/ a new 20 numbers.)

> -----Original Message-----
> From: news_at_liverpool.ac.uk (News System) [SMTP:news_at_liverpool.ac.uk]
> On Behalf Of qq45_at_liverpool.ac.uk (Ms. D.H. Harvey)
> Posted At: Wednesday, October 01, 1997 4:02 AM
> Posted To: misc
> Conversation: Sequence numbers jumping
> Subject: Re: Sequence numbers jumping
>
> Fenella Tan (fenella_at_voicenet.com) wrote:
> : 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
>
> If Oracle 7.3 same as Oracle 7.1.6 wrt sequences you could specify
> NOCACHE for them via CREATE or ALTER. This means values of sequence
> are not preallocated ie not held in memory and that increment by 1
> means increment by 1.
>
> Hope this helps. Helen
Received on Wed Oct 01 1997 - 00:00:00 CDT