Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 5 down vote accepted

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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