Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Reset sequence at midnight

Re: Reset sequence at midnight

From: Jonathan Lewis <>
Date: Wed, 14 Jan 2004 23:34:26 -0800
Message-ID: <>

Note in-line


Jonathan Lewis

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr

Next public appearance2:
 March 2004 Hotsos Symposium - Keynote
 March 2004 Charlotte NC - OUG Tutorial
 April 2004 Iceland

One-day tutorials:

Three-day seminar:
see ____UK___February

The Co-operative Oracle Users' FAQ

> Interesting, I've actually had to do this before.
> Be forewarned that this is not a good method to use for a very busy
> app, as it does introduce some level of serialization.
> control access to the sequence through a package
> Within the package use a function that sets a lock via dbms_lock.request
> and then immediately release the lock.

Did you request the lock in share mode to request next val ? This would help to reduce the contention. Normal users would then only queue on the exclusive lock that you would take for the fix-up.

    increment sequence

> The purpose of this will become clear in a moment.
> Create a procedure within the package that will be used to reset the
> sequence to 0. It is not necessary to drop the sequence to do this.
> eg.
> drop sequence s;
> create sequence s start with 100;
> select s.nextval from dual;
> declare
> vs integer;
> inc integer;
> junk integer;
> begin


> select s.nextval into vs from dual;
> inc := 0 - vs;
> execute immediate 'alter sequence s minvalue ' || inc;
> execute immediate 'alter sequence s increment by '||inc;
> select s.nextval into junk from dual;
> execute immediate 'alter sequence s increment by 1';


> end;
> /
> select s.nextval from dual;
> The procedure that does this just needs to take the same dbms_lock.request
> that the function mentioned earlier takes. The difference is that it does
> not
> release the lock until the modification of the sequence is completed.
> This forces any requests for new sequence numbers to wait for the
> modification
> to the sequence to complete.
> Jared

Please see the official ORACLE-L FAQ:
Author: Jonathan Lewis

Fat City Network Services    -- 858-538-5051
San Diego, California        -- Mailing list and web hosting services
To REMOVE yourself from this mailing list, send an E-Mail message
to: (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Thu Jan 15 2004 - 01:34:26 CST

Original text of this message