4

How do I re-initialize the sequence value without drop and recreating them again For example, the current sequence value is 3, now I want to restart it with 1 again.

0
6

This thread over on Stack Overflow has the information you are looking for. There are two main options:

  1. Drop the sequence and re-create it, or

  2. Use a trick, courtesy of Tom Kyte:

    either as a one-off:

    alter sequence seq increment by {minus_the_current_value} minvalue 0;
    

    or you can create a procedure if you need to do this regularly:

    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;
    /
    
1
  • Upvotes go to Jack... – dezso Apr 16 '13 at 9:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.