Gaps in Sequences in Non RAC databases

Gaps in Sequences may occur as a result of one of the following,

1. Rollback of a statement
2. Instance Crash or shutdown
3. Metadata aged out of the Data Dictionary (Use keep pool to avoid)
4. Process cleaned by PMON

As you can see above the number of instances have no part in obtaining Gaps. Hence you can get gaps both in RAC databases and non RAC single instance databases. Let us see an example of how we get Gaps in Single instance Databases.

Create sequence gapSeq start with 1 increment by 1 cache 20;

User A queries nextval and get 1
User B queries nextval and get 2
User B commits.
User A rollbacks.
User C queries nextval and get 3.

Hence the sequence value 1 is lost due to rollback of userA.

Also when you flush the shared pool the cached sequece numbers are lost.

Therefore the Gaps will occur in both the single instance and RAC databases..

Thanks