Re: sequence mystery

Problem was stated as the sequence number jumping, ie, you get a seguence value of 5 and the next sequence is 10. Also asked what is in last_number of all_sequences table (dictionary view).

Problem one may be related to 1) other processes are also using the sequence so you can not expect that your sequences will be in sequential order as the other processes have used them. 2) Your program or monitoring process could be 'eating' the numbers as you did not explain how you are looking at the numbers. And finally 3) I do not believe that oracle guarentees that the rdbms will issue the numbers sequentially. Oracle only guarentees that the numbers will be unique, but I have never seen them be anything other than sequential within an instance. If you are parallel then there can be big gaps between instances.

Depending on the parameters used when the sequence is created (cashe, nocashe, etc...) Oracle places a set number of values in a dictionary buffer for each sequence in the database and hands them out when requested. Shutdowns and crashes cause the loss of sequence values.

Hope this helps you find your answer.

Mark Powell -- the only advise that counts is the advise that you follow

                       so follow your own advise.