Setting Sequence Value to a Specific Number

There are times when we want to reset sequence value or set sequence value to a specific number. I have created following procedure to do that for me. It will set sequence to a specific number so that nextval will be the number which you want to be. This also handles the scenarios where sequence increment by is greater then one.

create or replace procedure set_sequence_nextval
(
  p_sequence in varchar2,
  p_value    in number default 1
)
is
  l_current_value number;
  l_increment_by  number;
begin

  execute immediate 'select ' || p_sequence || '.nextval from dual'
    into l_current_value;

  select increment_by
    into l_increment_by
    from user_sequences
   where sequence_name = upper(p_sequence);

  l_current_value := (l_current_value - p_value + l_increment_by) * -1;

  if (l_current_value <> 0) then

    execute immediate 'alter sequence ' || p_sequence || ' increment by ' || l_current_value || ' minvalue 0';

    execute immediate 'select ' || p_sequence || '.nextval from dual'
      into l_current_value;

    execute immediate 'alter sequence ' || p_sequence || ' increment by ' || l_increment_by || ' minvalue 0';

  end if;

end set_sequence_nextval;
/

Demonstration 1: Set Next Value to 101 when sequence increment by is 1

SQL> create sequence test_sequence start with 1 increment by 1 nocache nocycle;
Sequence created.

SQL> exec set_sequence_nextval('test_sequence',101);
PL/SQL procedure successfully completed.

SQL> select test_sequence.nextval from dual;
   NEXTVAL
----------
       101

SQL> select increment_by  from user_sequences where sequence_name = 'TEST_SEQUENCE';
INCREMENT_BY
------------
           1

Demonstration 2: Set Next Value to 51 when sequence increment by is 5

SQL> create sequence test_sequence start with 1 increment by 5 nocache nocycle;
Sequence created.

SQL> exec set_sequence_nextval('test_sequence',51);
PL/SQL procedure successfully completed.

SQL> select test_sequence.nextval from dual;
   NEXTVAL
----------
        51

SQL> select increment_by  from user_sequences where sequence_name = 'TEST_SEQUENCE';
INCREMENT_BY
------------
           5

Related Posts:
- Sequence Behavior with Multitable Insert All
- Auto Increment Column Performance Enhancement with each Oracle Version
- Oracle Auto Increment Column - Sequence as Default Value
- Sequence: NEXTVAL, CURRVAL and SESSION
- USER_SEQUENCES.LAST_NUMBER AND SEQUENCE CACHE