Re: Reset a Sequence

In article <9mm156$8u4$1_at_slb0.atl.mindspring.net>, "Roman says...
>
>Is there a way to have Oracle automatically reset a sequence on a schedule
>every morning?
>
>The database in question will use a primary key which includes the sequence
>and a date:
>
>0001-30AUG2001
>0002-30AUG2001
>0003-30AUG2001
> .
> .
>the next day:
>
>0001-31AUG2001
>0002-31AUG2001
>
>

see
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:951269671592

use dbms_job to schedule it.

If you have an opportunity to do so -- change your primary key. Just use a sequence in one column and a DATE in the other. Much cleaner. Since sequences guarantee there will be gaps -- resetting seems pointless -- you just need to sequence them (order them). You'll be able to find the min/max for a day - they don't need to be 1 and N.

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp