Re: Skippping Sequence Numbers

From: Stephen Clarke >>>
I have a sequence with a cache of 20. I am finding that the sequence is skipping values by amounts - 20, 18,19,20,40 - seeming to indicate the cache is being lost. I haven't had any instance terminations or had to shut the DB down  <<<

You are probably correct in your observation that the cache is being flushed. Try doing a count of the dba_sequences table where the cache is not null and compare this to your instance's init.ora setting for how many sequences are being allocated by sequence_cache_entries (number of sequences that can be cached in the SGA). I bet you have more sequences than you have reserved room for.

I suggest bumping this number up and reducing the number of cached entries for all low use sequences. These two actions should minimize the loss of sequenced generated numbers.

Mark Powell -- Oracle 7 Certified DBA
- The only advice that counts is the advice that you follow so follow your own advice - Received on Wed Sep 30 1998 - 17:43:01 CDT