How to Automatically Reset a Sequence's Value to 0 Every Year in Oracle 10G

How do I automatically reset a sequence's value to 0 every year in Oracle 10g?

Sequences aren't really designed to be reset. But there are some cases where resetting a sequence is desirable, for example, when setting up test data, or merging production data back into a test environment. This type of activity is not normally done in production.

IF this type of operation is going to be put into production, it needs to thoroughly tested. (What causes the most concern is the potential for the reset procedure to be accidentally performed at the wrong time, like, in the middle of the year.

Dropping and recreating the sequence is one approach. As an operation, it's fairly straightforward as far as the SEQUENCE goes:

    DROP SEQUENCE MY_SEQ;
    CREATE SEQUENCE MY_SEQ START WITH 1 INCREMENT BY 1 MINVALUE 0;

[EDIT] As Matthew Watson correctly points out, every DDL statement (such as a DROP, CREATE, ALTER) will cause an implicit commit. [/EDIT]

But, any privileges granted on the SEQUENCE will be dropped, so those will need to be re-granted. Any objects that reference the sequence will be invalidated. To get this more generalized, you would need to save privileges (before dropping the sequence) and then re-grant them.

A second approach is to ALTER an existing SEQUENCE, without dropping and recreating it. Resetting the sequence can be accomplished by changing the INCREMENT value to a negative value (the difference between the current value and 0), and then do exactly one .NEXTVAL to set the current value to 0, and then change the INCREMENT back to 1. I've used a this same approach before (manually, in a test environment), to set a sequence to a larger value as well.

Of course, for this to work correctly, you need to insure no other sessions reference the sequence while this operation is being performed. An extra .NEXTVAL at the wrong instant will screw up the reset. (NOTE: achieving that on the database side is going to be difficult, if the application is connecting as the owner of the sequence, rather than as a separate user.)

To have it happen every year, you'd need to schedule a job. The sequence reset will have to be coordinated with the reset of the YYYY portion of your identifier.

Here's an example:

http://www.jaredstill.com/content/reset-sequence.html

[EDIT]

UNTESTED placeholder for one possible design of a PL/SQL block to reset sequence

    declare
      pragma autonomous_transaction;
      ln_increment       number;
      ln_curr_val        number;
      ln_reset_increment number;
      ln_reset_val       number;
    begin

      -- save the current INCREMENT value for the sequence
      select increment_by
        into ln_increment
        from user_sequences
       where sequence_name = 'MY_SEQ';

      -- determine the increment value required to reset the sequence
      -- from the next fetched value to 0
      select -1 - MY_SEQ.nextval into ln_reset_increment from dual;

      -- fetch the next value (to make it the current value)
      select MY_SEQ.nextval into ln_curr from dual;

      -- change the increment value of the sequence to 
      EXECUTE IMMEDIATE 'alter sequence MY_SEQ increment by '
        || ln_reset_increment ||' minvalue 0';

      -- advance the sequence to set it to 0
      select MY_SEQ.nextval into ln_reset_val from dual;

      -- set increment back to the previous(ly saved) value
      EXECUTE IMMEDIATE 'alter sequence MY_SEQ increment by '
        || ln_increment ;
    end;
    /

NOTES:

• how to best protect the sequence from access while it's being reset, RENAME it?
• Several test cases to work through here.
• First pass, check normative cases of positive, ascending, increment 1 sequence.
• would a better approach be to create new SEQUENCE, add permissions, rename existing and new sequences, and then re-compile dependencies?

How do I reset a sequence in Oracle?

Here is a good procedure for resetting any sequence to 0 from Oracle guru Tom Kyte. Great discussion on the pros and cons in the links below too.

tkyte@TKYTE901.US.ORACLE.COM> 
create or replace
procedure reset_seq( p_seq_name in varchar2 )
is
    l_val number;
begin
    execute immediate
    'select ' || p_seq_name || '.nextval from dual' INTO l_val;

    execute immediate
    'alter sequence ' || p_seq_name || ' increment by -' || l_val || 
                                                          ' minvalue 0';

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

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

From this page: Dynamic SQL to reset sequence value

Another good discussion is also here: How to reset sequences?

How to reset sequence number every last day of the ending year automatically?

First you have to create a procedure which contain the SQL as you mention. Then you have to create a schedule job which will execute the procedure and this job will run once in a year.

CREATE OR REPLACE PROCEDURE RESET_SEQUENCE
IS
v_next_value number;
v_sequence_name varchar2(100):='SEQUENCE01';
begin
    execute immediate   'select ' || v_sequence_name || '.nextval from dual' INTO v_next_value;
    execute immediate   'alter sequence ' || v_sequence_name || ' increment by -' || v_next_value || ' minvalue 0';
    execute immediate  'select ' || v_sequence_name || '.nextval from dual' INTO v_next_value;
    execute immediate  'alter sequence ' || v_sequence_name || ' increment by 1 minvalue 0';
END RESET_SEQUENCE;
/

BEGIN
  SYS.DBMS_SCHEDULER.CREATE_JOB
    (
       job_name        => 'RESET_SEQ_YEARLY'
      ,start_date      => TO_TIMESTAMP_TZ('2016/01/01 10:20:41.299669 Asia/Dacca','yyyy/mm/dd hh24:mi:ss.ff tzr')
      ,repeat_interval => 'FREQ=YEARLY;BYMONTH=DEC; BYMONTHDAY=31;BYHOUR=23;BYMINUTE=59;BYSECOND=59'
      ,end_date        => NULL
      ,job_class       => 'DEFAULT_JOB_CLASS'
      ,job_type        => 'PLSQL_BLOCK'
      ,job_action      => 'begin
                                    RESET_SEQUENCE;
                                    end;'
      ,comments        => 'Last day of the year'
    );
   SYS.DBMS_SCHEDULER.ENABLE
    (name                  => 'RESET_SEQ_YEARLY');
END;
/

How to reset automatically the oracle sequence number before it reach to the maximum number

The simplest way is to define sequence as CYCLE:

create sequence myseq minvalue 10000 maxvalue 99999 increment by 1 CYCLE;

It will start from minvalue again when reaches maxvalue, i.e. generate numbers in cycle.

Oracle spec says:

CYCLE - Specify CYCLE to indicate that the sequence continues to generate values after reaching either its maximum or minimum value.
After an ascending sequence reaches its maximum value, it generates
its minimum value. After a descending sequence reaches its minimum, it
generates its maximum value.

Need to reset the value of sequence in Oracle

Reasons why you shouldn't reset the value if it's being used:

What happens if you have 20 records and delete records 5-10? You have a gap in the middle that re-setting the sequence will not solve. Sequences will never generate a gap free sequence of numbers, a perfect 1, 2 .. n.

If you call .nextval and don't use the value it's gone. Are you going to drop and re-create the sequence? If you start an insert and cancel it and Oracle rolls back what you've done those values are gone. If you set nocache then you will have less gaps but at a cost of a hit to performance; is it worth it?

Your cache should be set to the number of inserts you expect to do at any one time across all sessions to avoid any performance issues. Sequences are designed to provide a very quick, scalable way of creating a surrogate key without any locks etc not to re-generate the set of positive integers.

At the end of the day it shouldn't matter in the slightest. If you're relying on an unbroken sequence as the key of your table then you have a problem with your data rather than sequences.

---

Answering the question:

To actually answer your question you would need to:

1. Firstly, find out what the maximum id (sequence) value in your table is.
2. Then drop and re-create the sequence.

Finding the maximum value means you'd need to re-create the sequence dynamically at the cost of another hit to performance.

If you try to insert something into your table whilst this is happening it will fail, and may invalidate any triggers or other objects which use the sequence:

declare

   l_max_value number;

begin

   select max(id)
     into l_max_value
     from my_table;

   execute immediate 'drop sequence my_sequence_name';

   -- nocache is not recommended if you are inserting more than
   -- one row at a time, or inserting with any speed at all.
   execute immediate 'create sequence my_sequence_name
                           start with ' || l_max_value
                      || ' increment by 1
                           nomaxvalue
                           nocycle
                           nocache';

end;
/

As I say this is not recommended and you should just ignore any gaps.

---

Update - aka A Better Answer Thanks to Jeffrey Kemp:

Contrary to the documentation's recommendation there is, as Jeffrey Kemp suggested in the comments, a way to do this without dropping and re-creating the sequence.

Namely, by:

1. Working out the difference between the maximum id in your table and the current value of the sequence.
2. Altering the sequence to increment by this negative number
3. Altering the sequence to increment by 1 again.

The benefits of this are that the object still exists so and triggers, grants etc are still maintained. The downside, as I see it, is that if another session increments by this negative number at the same time as yours you can go back too far.

Here's a demonstration:

Set up the test:

SQL> create sequence test_seq
  2   start with 1
  3   increment by 1
  4   nomaxvalue
  5   nocycle
  6   nocache;

Sequence created.

SQL>
SQL> create table tmp_test ( id number(16) );

Table created.

SQL>
SQL> declare
  2     l_nextval number;
  3  begin
  4
  5    for i in 1 .. 20 loop
  6       insert into tmp_test values ( test_seq.nextval );
  7    end loop;
  8
  9  end;
 10  /

PL/SQL procedure successfully completed.

SQL>
SQL> select test_seq.currval from dual;

   CURRVAL
----------
        20

SQL>
SQL> delete from tmp_test where id > 15;

5 rows deleted.

SQL> commit;

Commit complete.

Revert the sequence

SQL>
SQL> declare
  2
  3     l_max_id number;
  4     l_max_seq number;
  5
  6  begin
  7
  8     -- Get the maximum ID
  9     select max(id) into l_max_id
 10       from tmp_test;
 11
 12     -- Get the current sequence value;
 13     select test_seq.currval into l_max_seq
 14       from dual;
 15
 16     -- Alter the sequence to increment by the difference ( -5 in this case )
.
 17     execute immediate 'alter sequence test_seq
 18                          increment by ' || ( l_max_id - l_max_seq );
 19
 20     -- 'increment' by -5
 21     select test_seq.nextval into l_max_seq
 22       from dual;
 23
 24     -- Change the sequence back to normal
 25     execute immediate 'alter sequence test_seq
 26                          increment by 1';
 27
 28  end;
 29  /

PL/SQL procedure successfully completed.

SQL>
SQL> select test_seq.currval from dual;

   CURRVAL
----------
        15

SQL>

Reset Oracle sequence which increments by 50

You should check for errors and print out the SQL statements you want to execute. Then you wouldn't have to both with Stack Overflow:

EXECUTE IMMEDIATE 'SELECT '||p_seq_name||'.NEXTVAL FROM DUAL' INTO l_val; 

    l_val := l_val - l_minvalue; 

    EXECUTE IMMEDIATE 'ALTER SEQUENCE '||p_seq_name||'INCREMENT BY -'||l_val|| 
    'MINVALUE '||l_minvalue; 

    EXECUTE IMMEDIATE 'SELECT '||p_seq_name||'.NEXTVAL FROM DUAL' INTO l_val; 

    EXECUTE IMMEDIATE 'ALTER SEQUENCE '||p_seq_name||' INCREMENT BY 50 MINVALUE ' 
------------------------------------------------------^ space
    ||l_minvalue; 
END; 

For this reason, I prefer using replace() to construct such strings. For instance:

v_sql := 'ALTER SEQUENCE [p_seq_name] INCREMENT BY -[l_val] MINVALUE [l_minvalue]';
v_sql := replace(v_sql, '[p_seq_name]', p_seq_name);
v_sql := replace(v_sql, '[l_val]', l_val);
v_sql := replace(v_sql, '[l_minvalue]', l_minvalue);

Best way to reset an Oracle sequence to the next value in an existing column?

These two procedures let me reset the sequence and reset the sequence based on data in a table (apologies for the coding conventions used by this client):

CREATE OR REPLACE PROCEDURE SET_SEQ_TO(p_name IN VARCHAR2, p_val IN NUMBER)
AS
   l_num   NUMBER;
BEGIN
   EXECUTE IMMEDIATE 'select ' || p_name || '.nextval from dual' INTO l_num;

   -- Added check for 0 to avoid "ORA-04002: INCREMENT must be a non-zero integer"
   IF (p_val - l_num - 1) != 0
   THEN
      EXECUTE IMMEDIATE 'alter sequence ' || p_name || ' increment by ' || (p_val - l_num - 1) || ' minvalue 0';
   END IF;

   EXECUTE IMMEDIATE 'select ' || p_name || '.nextval from dual' INTO l_num;

   EXECUTE IMMEDIATE 'alter sequence ' || p_name || ' increment by 1 ';

   DBMS_OUTPUT.put_line('Sequence ' || p_name || ' is now at ' || p_val);
END;

CREATE OR REPLACE PROCEDURE SET_SEQ_TO_DATA(seq_name IN VARCHAR2, table_name IN VARCHAR2, col_name IN VARCHAR2)
AS
   nextnum   NUMBER;
BEGIN
   EXECUTE IMMEDIATE 'SELECT MAX(' || col_name || ') + 1 AS n FROM ' || table_name INTO nextnum;

   SET_SEQ_TO(seq_name, nextnum);
END;

Automatically setting Oracle's sequence start value

DECLARE
    MAXVAL MY_TABLE.ID%TYPE;
BEGIN
    SELECT NVL(MAX(id),1) INTO MAXVAL FROM MY_TABLE;
    EXECUTE IMMEDIATE 'CREATE SEQUENCE MY_TABLE_SEQ START WITH ' || MAXVAL || ' INCREMENT BY 1';
END
/

You could also ALTER the sequences once they are created.

Some readings about the subject: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:951269671592

---

Related Topics