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.
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
|
| "Oracle" <Oracle_list@hotmail.com>
Sent by: ml-errors@fatcity.com
01/14/2004 09:04 AM
Please respond to ORACLE-L
|
To: Multiple recipients of list ORACLE-L <ORACLE-L@fatcity.com>
cc:
Subject: Reset sequence at midnight |
Hi,
I have a sequence which i want to reset to 0 at midnight everyday.
What is the best way to do this?
Db version - 9.2.0.1.0
Thanks
Imran
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Oracle
INET: Oracle_list@hotmail.com
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@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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
INET: Jared.Still_at_radisys.com
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).
Received on Wed Jan 14 2004 - 13:09:30 CST
