Sequence increase by 20

[brokenbone]

Hi all!

In Oracle 10 I noticed that sequence sometimes increases for 20 instead for 1. Since 20 is default 'Cache size' for sequences, I wonder if this is the case. Has anyone else noticed this? I haven't found out what must happen in the meantime (is it enough just that some time pases by or maybe an execution of some ALTER... querry) as if I select NEXTVAL immediate, the sequence increases for 1. (Could this be Oracle bug?) Is there another way to solve this other than to have no caching foe sequence?

Thanks, bye!

PS
I apologize for my english and hope you understand ehat I mean .

 

[Welshbird]

I had a aimilar issue, and found it was because I hadn't dropped and then re-created the sequece, hence it 'carried on' from where it finished rather than from 1.

Maybe this helps?

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[SantaMufasa]

BrokenBone,

Your English is just fine...no problem understanding your question.

The overall benefit of sequence caching is speed: the higher the caching value, the fewer times that Oracle must go to the database and obtain a cache of sequences. The negative effect of caching is that once a cache of sequences exists, if there is a re-boot of the database, Oracle abandons any values that were in the cache. Therefore, this is not an Oracle bug...this is expected behaviour.

Therefore, if you do not want to leave gaps in your sequence values, then you must pay the price by setting your sequence to NOCACHE.

My question is this: What business risk do you suffer by leaving gaps in your sequence values? For practical purposes, even if you assigned sequence values by "10,000", there are not too many business applications that would ever run out of the octillion (10[sup]27[/sup]) values that an Oracle sequence can provide.

If you do use too many numbers, let me know and I will be happy to pay your invoice from the Ministry of Numbers in your country.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[brokenbone]

Hi all!

Thanks for replies! Mustafa: there is actualy nothing wrong with sequence increasing for 20, it's just that sometimes we have this generated numbers visible to users and then they ask why their new document doesn't have the number 2 but number 21 for example. Thats all. As I said, nothing is actualy wrong with this.

Thanks, bye!

 

[Beilstwh]

I had a case where I had an existing packaged application that used a maximum of 999999 document numbers. Since the particular database was being shut down every night for a cold backup, the cache was killing me, so I turned it off. (just an example). If you have a system that needs a sequence number every few minutes, your cache setting is not going to matter. If you need a hundred sequence numbers a second, it is going to make a major difference in performance. What is your requirements?

Bill
Oracle DBA/Developer
New York State, USA