You can avoid the problem by changing the sequences as follows:
ALTER SEQUENCE XXX NOCACHE;
Actually "avoid the problem" means avoid the problem with sequence gaps.
But you cannot avoid the problem; there is no such thing as a gapless sequence. For example, even with nocache set if someone calls seq.nextval and doesn't insert the value somewhere (or does insert it and later rolls back their transaction) that number is gone.
Tamilselvan, Carlos : sorry for the delay, but the answer "HIGHWATER" given by Carlos is plain wrong : as said by HJR in the page that I cited (at the bottom of the page, read carefully) the correct answer is NEXTVALUE. My tests in a 9ir2 EE database :
SQL*Plus: Release 22.214.171.124.0 - Production on Qua Jan 3 14:55:46 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> conn sys as sysdba
==> user SCOTT have a sequence...
SQL> select nextvalue, min_value, max_value, cache_size, highwater from v$_sequences
2 where sequence_name='SEQ_DEPT' and sequence_owner='SCOTT';
Yes, after a SHUTDOWN ABORT (or similar db stops) all in the caches is lost, this is one of the "issues" with it - and at a level, with v$ and the like, consistency do not plays a role here... If it fits the bill, use it, but using the value in NEXTVALUE not in HIGHWATER....
hi , why do the sequence numbers are missing if i use
there is no shutdown abort, nor clearing the shared pool etc...
is there any possibility at users side i.e application side.??
if u suggest use NOCACHE option, then how to utilize cache option?