Oracle Sequence

[Marko_3]

Dear all

I am using Oracle sequence in my my stored procedure.

select my_seq.nextval
into l_seq
from dual;

However I discover that the sequence generated has gaps occasionally. For example, it was okay for a while from 1 to 500. But the next time it actually begins at 631 !!!

Why does this happen and how can I overcome this problem?

Thanks

[Howard Clare]

Hi

There are a number of things to check
1) Is the interval being changed ever?
2) I assume it's "nocycle" - ie doesn't cycle to minvalue when you get to maxvalue
3) Other users are not selecting from the sequence unknown to you
4) - This is probably it - If the stored procedure doesn't commit the transaction - I'm assuming you're putting the sequence values into a table - the stored procedure has still selected from the sequence and incremented value. This is usually how gaps in sequence numbers occur.

Regards
Howard

[Manuel P. Ron]

Look at cached values... If it is at 20, by example, it means that after a connection using the sequence with the command NEXTVALUE and CURRVALUE the sequence stops at 35, next time will start at 55 'cause you specified 20 values cached for any session. To avoid it, put nocache option.

[Manuel P. Ron]

Please, assign points or don't use this board

"This member has assigned points to 0 of 58 responses to his/her questions"

[Praveen Bezawada]

Hi
You get gaps in sequence when the cache option is set. Every 'shutdown abort' causes the cached sequences to be lost. If you are particular about gaps 'create sequence' with nocache option.
Set cycle option if you want the sequence numbers to be recycled after reaching the maximum.
Coming back to gaps in sequence, we had got gaps even with cache option set to false when more processes were using the sequence.nextval, oracle seems to be allocating sequences to processes in bulk though I am not sure.
Hope this helps

...BPK...