Oracle sequences reset

Question

I've created a sequence in my Oracle database. It will increment by 1 every time when i started my application and inserted in the table. The table look like this:

create table COUNTERS_DELEGATION
(
  counter_id     NUMBER not null,
  counter_number LONG not null,
  current_date   NUMBER not null
)

In the field current_date i will insert the current year.

My question is: When the year increment for example from 2016 to 2017 i want to start my sequence again from initial value 1. Is this possible?

Another duplicate: http://stackoverflow.com/q/34587217/330315 — , Nov 02 '16 at 10:36
So every year you're going have a process which must be run prior to any numbers being allocated from this sequence which will reset it to 1? I think that's a bad design decision. I'm positive there are very good ways to accommodate this requirement (that some field increment from 1 at the beginning of each year) without the need to reset a sequence. — Bob Jarvis - Слава Україні, Nov 02 '16 at 11:15

score 0 · Answer 1 · answered Nov 02 '16 at 10:31

0

If I understand your request correctly, you want to reset the sequence when the value of the sequence gets to 2017.

If so, I think you'll need to drop and recreate the sequence:

DROP SEQUENCE sequencename;

CREATE SEQUENCE sequencename
MINVALUE 1
MAXVALUE 2017
START WITH 1
INCREMENT BY 1
CYCLE;

answered Nov 02 '16 at 10:31

bbrumm

1,342
1
8
13

Not at all. I want to reset the sequence when the date changes for example now we have 2016 year and when we will have 2017 the sequence should be reset. Do you have any idea how to implement that? – k1dl3r Nov 02 '16 at 10:34
@k1dl3r: you can't "reset" a sequence. You have to drop and re-create it. – Nov 02 '16 at 10:36

Oracle sequences reset

1 Answers1