On a few occasions when people have asked me, “Why do Oracle sequences miss numbers?” I’ve failed to provide what I felt was a proper explanation. I was familiar with the CACHE option for sequence definition, but there was something missing… That missing thing was V$_SEQUENCES!
I guess I’d never really looked into it properly in the past, but having now read a couple of excellent presentations from Joel Goodman and a post from Fredrick Tang my understanding of sequences has greatly increased.
The key point I learnt from the articles is that if something causes a cached sequence to be removed from the dictionary (row) cache before all the previously cached sequence numbers have been used then the values from V$_SEQUENCES.NEXTVAL to V$_SEQUENCES.HIGHWATER will be lost from the sequence.
If you want to know more then please go and Keeping Your Sequences in Order in the first instance.