Re: Problem with Sequences in Oracle 8.0.5

When you recreated it without cache' do you mean you recreated it using the term NOCACHE explicitly, or did you just eliminate the 'CACHE 20'.

In the absence of a specific directive, the default is CACHE 20 - so my guess is that your sequence is still cached.

If this is a high usage sequence, you don't really want to make it NOCACHE - the overhead gets a bit high. Also, even with NOCACHE it is still possible to 'lose' values in the data set (e.g. a user acquires the sequence, then rolls back the insert).

Don't worry too much about losing values - it's the way sequences work: you can reduce the impact of the problem in your version of Oracle (I think) by adjusting an init.ora parameter called something like 'sequence_cache_entries' (which also happens to default to 20). I think 8.0.5 also allows you to use the dbms_pool package to KEEP sequences, again reducing the problem.

--
Jonathan Lewis

Seminars on getting the best out of Oracle
Last few places available for Sept 10th/11th
See http://www.jlcomp.demon.co.uk/seminar.html




Eyvind Elvestrand wrote in message <9klvke$61b$1_at_snipp.uninett.no>...


>Have an inventory database running on NT4/Oracle 8.0.5.0.0.

>The GUI is programmed in Delphi 5 / Odac (ODI)

>For continuous number series I use Oracles's sequence "machinery", like

>this:

>select SEQ_INV_NR.nextval from DUAL

>

>Now the select statement has begun returning higher numbers than expected -

>typically 20 higher!

>

>Originally the sequence was created like this :

>CREATE SEQUENCE SEQ_INV_NR

>START WITH 214575

>INCREMENT BY 1

>NOMAXVALUE

>NOCYCLE

>CACHE 20

>

>I suspected the cache part, dropped the sequence and recreated it without

>cache - no good.

>

>Has anybody got a clue!

>

>Best regards from Eyvind.

>

>

>

>

>

>

>