How do I reset a sequence in Oracle?

Question

In postgresql I can do something like this:

ALTER SEQUENCE serial RESTART WITH 0;

Is there a oracle equivalent?

Answer 1

Have a look at "Sequence resets" here.

Answer 2

A true restart is not possible afaik. (Please correct me if I'm wrong!).

However, if you want to set it to 0, you can just delete and recreate it.

If you want to set it to a specific value, you can set the INCREMENT to a negative value and get the next value.

i.e if your sequence is at 500, you can set it to 100 via

ALTER SEQUENCE serial INCREMENT BY -400;
SELECT serial.NEXTVAL FROM foo;
ALTER SEQUENCE serial INCREMENT BY 1;

Answer 3

altering the sequence's INCREMENT value, incrementing it, and then altering it back is pretty painless, plus you have the added benefit of not having to re-establish all of the grants as you would had you dropped/recreated the sequence.

Answer 4

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.

[email protected]> 
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?

Answer 5

This Stored Procedure restarts my sequence:

Create or Replace Procedure Reset_Sequence  
  is
  SeqNbr Number;
begin
   /*  Reset Sequence 'seqXRef_RowID' to 0    */
   Execute Immediate 'Select seqXRef.nextval from dual ' Into SeqNbr;
   Execute Immediate 'Alter sequence  seqXRef increment by - ' || TO_CHAR(SeqNbr) ;
   Execute Immediate 'Select seqXRef.nextval from dual ' Into SeqNbr;
   Execute Immediate 'Alter sequence  seqXRef increment by 1';
END;

/

Answer 6

1) Suppose you create a SEQUENCE like shown below:

CREATE SEQUENCE TESTSEQ INCREMENT BY 1 MINVALUE 1 MAXVALUE 500 NOCACHE NOCYCLE NOORDER

2) Now you fetch values from SEQUENCE. Lets say I have fetched four times as shown below.

SELECT TESTSEQ.NEXTVAL FROM dual SELECT TESTSEQ.NEXTVAL FROM dual SELECT TESTSEQ.NEXTVAL FROM dual SELECT TESTSEQ.NEXTVAL FROM dual

3) After executing above four commands the value of the SEQUENCE will be 4. Now suppose I have reset the value of the SEQUENCE to 1 again. The follow the following steps. Follow all the steps in the same order as shown below:

a) ALTER SEQUENCE TESTSEQ INCREMENT BY -3;
b) SELECT TESTSEQ.NEXTVAL FROM dual
c) ALTER SEQUENCE TESTSEQ INCREMENT BY 1;
d) SELECT TESTSEQ.NEXTVAL FROM dual

Answer 7

Recreate the sequence with the last id + 1 of some table... Easier

Answer 8

use CYCLE when you create the sequence.

Answer 9

this is my approach:

1. drop the sequence
2. recreate it

Example:

--Drop sequence

DROP SEQUENCE MY_SEQ;

-- Create sequence

create sequence MY_SEQ minvalue 1 maxvalue 999999999999999999999 start with 1 increment by 1 cache 20;

Answer 10

I used a process specified by dougman inside a trigger and I got an error stating 'cannot commit inside a trigger'. What can I do to use it inside a trigger??