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>