Re: Sequence NextVal jumping 18 on reboot!

This can easily be corrected by issuing the following....

alter sequence <sequence_name> nocache;

Cheers,

Robert
adrianh_at_globalnet.co.uk (Adrian Harrison) wrote:

>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

Robert Prendin
RP Data Solutions Inc.
Specializing in ORACLE DBA Support Services Received on Fri Jan 15 1999 - 15:04:54 CST