Sequence increment by 50 instead of 1

Question

I created an Oracle sequence:

Create sequence seq_acteurs start with 1 increment by 1;

Normally the sequence must be incremented by 1, but when insert into Acteurs table, the sequence s incremented by 50! That doesn't seem logical. Why is this so?

Any help will much apreciated.

Answer 1

Sequence numbers are not fetched one at a time when you need them. NOrmally, the DB caches a certain number of sequence numbers in memory. WHen the cached sequences are all used up, then the DB fetches a new block of sequence numbers into cache. Now if the DB crashes for some reason while the cache still has some unused numbers, then there could be some gaps in your sequence numbers.

Answer 2

Sequences does not guarantee that they generate a gap free sequence of numbers.

You can minimize the gaps by specifying nocache, but this does still not guarantee gap free sequences, and might create a resource contention.

Example from the documentation:

CREATE SEQUENCE customers_seq START WITH 1000 INCREMENT BY 1 NOCACHE NOCYCLE;