Re: Sequence missing values

Could be due to sequence caches (set in initSID.ora, default 20), I believe when you shutdown the database the cached sequence numbers are/may be lost.

The other possibility is that your user is encountering a failure between getting the next sequence number and storing data in the table - each such failure will loose a sequence number.

Oracle sequences are not a guarantee of getting a complete sequence. You would need to use another mechanism is you require a pure sequence e.g.

Create a single row table to hold all the sequence numbers you require, select the next value as required;

Update my_sys_table set abc_seq = abc_seq + 1; Select my_sys_table.abc_seq into v_seq;

Insert into my_abc_table values (v_seq, ..........); commit;

If a failure occurs the sequence select/update will be undone as well. There are some locking issues with this method!

Phil Britton wrote in message <361cc5ca.21219522_at_pub.news.uk.psi.net>...
>After the procedures have been run it appears that the sequence has
>been missing certain values. e.g a value that we had expected to be
>1001 is actually 1009. The jump seems to be random. Does anyone have
>*any* idea what could be causing this. Apart from the obvious ones
>like somebody else getting the nextval, which is not happening.
>
>thanks
>
>
>Phil Britton
>PrismTech (UK)
Received on Fri Oct 09 1998 - 03:03:30 CDT