Re: Sequence NextVal jumping 18 on reboot!

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 15 Jan 1999 20:59:13 -0000
Message-ID: <916433970.2111.2.nnrp-10.9e984b29@news.demon.co.uk>

As two other posters have noted, the sequence value stored on disc is incremented by the sequence's 'cache size'. If you crash the database (by rebooting the PC without a proper Oracle shutdown), the values between the current value and the value stored on disc are lost.

If you shut the database down cleanly before rebooting then the current value is written back to disc before shutdown, and your values will not go missing.

(At least, that's how it used to work, but I haven't checked it for several years).

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

Adrian Harrison wrote in message <369f1c53.196021_at_news.globalnet.co.uk>...
>Using NT4 Server, Personal Oracle 7.3.4
>
>Please someone tell me I'm not going stupid but every time I reboot my PC
the NEXTVAL in a very
>simply sequence (see below) increases by 18!
>
>For example if the last CRAFT_ID used was 3 on reboot the new one's 21
>
>Here is the code - generated by Oracle Migration Wizard for MS Access!
>
>///////////////////////////////////////////////////////////////////////////

/////////////////

>CREATE SEQUENCE SEQ_NLS_CRAFTS
>INCREMENT BY 1 START WITH 1
>/
>CREATE TRIGGER TRG_NLS_CRAFTS
>BEFORE INSERT OR UPDATE ON NLS_CRAFTS
>FOR EACH ROW
>DECLARE
> iCounter NLS_CRAFTS.CRAFT_ID%TYPE;
> cannot_change_counter EXCEPTION;
>BEGIN
> IF INSERTING THEN
> Select SEQ_NLS_CRAFTS.NEXTVAL INTO iCounter FROM Dual;
> :new.CRAFT_ID := iCounter;
> END IF;
>
> IF UPDATING THEN
> IF NOT (:new.CRAFT_ID = :old.CRAFT_ID) THEN
> RAISE cannot_change_counter;
> END IF;
> END IF;
>
>EXCEPTION
> WHEN cannot_change_counter THEN
> raise_application_error(-20000, 'Cannot Change Counter Value');
>END;
>/
>///////////////////////////////////////////////////////////////////////////

/////////////////

>
>I can't for the life of me work out what the hell's going on!
>
>Any ideas
>
>Thanks
>
>Adrian Harrison
Received on Fri Jan 15 1999 - 14:59:13 CST