| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Sequence missing values
On Thu, 08 Oct 1998 14:06:23 GMT, phil_at_paule.demon.co.uk (Phil
Britton) wrote:
>We're using a single sequence within a schema that is called in
>various procedures that are run to set up a database. THe sequence is
>owned by the schema and only one session is running on this schema.
>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.
Your sequences are probably cached in SGA - look for the value of your SEQUENCE_CACHE_ENTRIES init parameters. So when you shutdown the database or when it crashes, the unused cached sequence numbers will be lost. If you would like to avoid this, then create your sequences with the NOCACHE option. But in this case be aware of the performance impact because each sequence number will then be fetched from data dictionary.
However, no matter if you cache the sequences in SGA or not, there might allways be gaps in sequence numbers written to the database. Suppose you select the sequence number inside your transaction. Then you rollback the transaction. You repeat the transaction and get the next sequence number (unless you use sequence.currval). You commit this second transacton. As a result, the first sequence number will be missing from your data.
>thanks
>
>
>Phil Britton
>PrismTech (UK)
HTH,
--
Jurij Modic <jmodic_at_src.si>
Certified Oracle7 DBA (OCP)
 |
 |