Re: Reset sequence at midnight

Note in-line

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  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:
http://www.jlcomp.demon.co.uk/tutorial.html

Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html ____UK___February

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Wednesday, January 14, 2004 7:09 PM

> 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.

    lock(shared)
    increment sequence
    release

>
> 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

        lock(exclusive)

> 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';

        release()

> 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: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (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).