Re: Strange problem with Sequence.. please help..

Brian Peasland wrote:
> > If you restart the Oracle instance/database
> > the cached numbers will be lost.
>
> Additionally, the sequence number are cached in the Dictionary Cache.
> Depending on activity and the configuration of your server, it is
> possible for the
> sequence information to get aged out of the cache. So you can lose
> sequence numbers without a restart of the instance.
>
> HTH,
> Brian

Brian,

Thanks for the additional information regarding sequence numbers potentially aging out of the dictionary cache. I have never noticed this happening, but my sequences see a lot of activity.

For any one interested, the Oracle Database Performance Tuning Guide for 10g offers the following information regarding sequences (paraphrased of course):

The dictionary cache includes usernames, segment information, profile data, tablespace information, sequence numbers, and descriptive information about schema objects.

Allocating sufficient cache space for frequently updated sequence numbers significantly reduces the frequency of dictionary cache locks. Use the CACHE keyword on the CREATE SEQUENCE or ALTER SEQUENCE statement to configure the cached number for each sequence.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Fri Jul 21 2006 - 14:49:35 CDT