Reset sequence in Oracle annually

Question

I have a sequence for a column (NOT the Primary Key) of my table that increments with every single insert.

I want every 1st day of the new year to reset that sequence to value 1.

I have seen some solutions to drop and re-create the sequence. But I want to schedule it annually.

How is that possible?

Thanks in advance

Why don't you simply schedule once a year within crontab (supposing you're using unix) that launches a small sql script that recreates your sequence? Or even more simple: use DBMS_SCHEDULER — Robert Kock, May 29 '18 at 12:11

score 5 · Accepted Answer · answered May 29 '18 at 12:16

You can use DBMS_SCHEDULER Oracle package to schedule jobs. See more details here: https://docs.oracle.com/cd/B28359_01/server.111/b28310/scheduse.htm

Create a procedure which resets the sequence and run that procedure as a scheduled job every year. Here is an example of such procedure: How do I reset a sequence in Oracle?

Haitham Mostafa Bayoud · Answer 2 · 2021-09-30T09:01:04.750

Try My Own Procedure

    create or replace procedure reset_seq_hh_ww(p_seq_name in varchar2,
                                            num_seq    in Number) is
  l_val     number;
  curr_se   number;
  min_l_val number;
begin

  execute immediate 'select ' || p_seq_name || '.nextval from dual'
    INTO l_val;
  DBMS_OUTPUT.PUT_LINE('1-l_val='||l_val);
  if l_val > num_seq then
    min_l_val := -1 * l_val;
    
    execute immediate
    
     'alter sequence ' || p_seq_name || ' increment by ' ||  min_l_val || ' minvalue 0';
      DBMS_OUTPUT.PUT_LINE('alter sequence ' || p_seq_name || ' increment by ' ||  min_l_val || ' minvalue 0');

  
  
    execute immediate
    
     'select ' || p_seq_name || '.nextval from dual'  INTO l_val;
     DBMS_OUTPUT.PUT_LINE('select ' || p_seq_name || '.nextval from dual');
     DBMS_OUTPUT.PUT_LINE('2-min_l_val='||min_l_val);
    
    DBMS_OUTPUT.PUT_LINE('3-l_val='||l_val);
    

    curr_se := num_seq-1;
    if curr_se=0 then curr_se:=2-num_seq;
    else
     curr_se := num_seq-1;
     end if; 
    DBMS_OUTPUT.PUT_LINE('4-curr_se='||curr_se);
    
    
  else
  
    curr_se := num_seq - l_val-1;
    if curr_se =0 then curr_se:=curr_se+num_seq;
    else
      curr_se:=curr_se;
      end if;
    DBMS_OUTPUT.PUT_LINE('5-curr_se='||curr_se);
  end if;
  execute immediate
  /*'alter sequence ' || p_seq_name || ' increment by -' || l_val ||
                                                            ' minvalue 0';
          DBMS_OUTPUT.PUT_LINE (l_val);
  
  
            execute immediate*/
   'alter sequence ' || p_seq_name || ' increment by ' || curr_se ||
   ' minvalue 0';
  DBMS_OUTPUT.PUT_LINE('6-l_val='||l_val);

  execute immediate 'select ' || p_seq_name || '.nextval from dual'
    INTO l_val;

  DBMS_OUTPUT.PUT_LINE('7-l_val='||l_val);

  
  execute immediate 
  'alter sequence ' || p_seq_name ||   ' increment by 1 minvalue 0';
end;

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). — Community, Sep 30 '21 at 07:40

Reset sequence in Oracle annually

2 Answers2