Ever wonder why there are missing numbers when you get the NEXTVAL of the oracle sequence? We’ll it turns out that the sequence cache is affecting this. Basically, your session is assigned with a range of numbers, say 20(default). So when you logout of your session and then login and hit the sequence again, you’ll have a gap of 20.
The solution is to set the cache to a small number or explicitly don’t set a cache at all. The only downside here is the contention between user sessions. Moreover, be aware that this will only minimize the gap. There will always be gaps and we have to live with it.
CREATE SEQUENCE MY_SEQUENCE_NAME
START WITH 1
INCREMENT BY 1
As a side note, this cause problem on one of my interface programs. A vendor required that all files we send them will follow a sequence. We’ll, we’re sending it in sequence but they did not tell us that there should be no skipping of numbers. It’s something that the interface system cannot handle. But since I wasn’t aware of the CACHING issue, I instead used a manual generation of sequence number by creating a custom function for it. I’ll stick with that for that system because sequence will gaps. See the AskTom article for details.