Re: sequence #'s skipping

Yes, this is a common occurrence.

Your sequence is set up to cache 20 entries in the SGA. Verify this by looking at user_sequences, all_sequences or dba_sequences (cache_size). Some reasons you may be experiencing gaps are:

1. Shutting down the database abnormally (shutdown abort). I have a feeling you may be using NT as your OS. Doing a OS shutdown on NT (without first shutting down the DB) is equivalent to doing a shutdown abort in Oracle. This will cause your sequence (on any platform) to lose the amount of entries it has cached in the SGA (even if you have specified "order"). Shutting down your DB normally (shutdown or shutdown immediate) should not cause you to lose sequence numbers.
2. You can also lose sequence numbers by when they are aged out of the SGA

     or having initSID.ora parameter sequence_cache_entries set too low.

You cannot be a guaranteed that you will never lose a sequence number since once they are popped they are considered used whether or not you actually use it
in your code. You can, however, reduce the occurrence of gaps by: 1) not caching the sequence at all; 2) Shutting down your DB cleanly; 3) pinning the sequence in the SGA using package dbms_shared_pool (7.3.3 or 7.3.2.3 with a patch from Oracle).

Steve Lehrfeld wrote:

> we found that Oracle 7 skips sequence #'s after it has been reset. It
> always rounds to the nearest twenty, i.e. 10001, 10002, <reset>, 10020,
> 10021, 10022, <reset>, 10040, etc.
>
> Does anyone know why it does this and how to prevent it?
>
> -steve
Received on Tue Dec 16 1997 - 00:00:00 CST