Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2005
    Posts
    276

    Unanswered: refreshing Sequences

    Can I refresh a sequence in Oracle. I want the sequence to start again from the start again.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    There's no refresh; however, there are two ways of how to do it (at least, two that I can tell you about): first one is a simple one: drop it and create a new one. Another one requires a few SQL statements and can not be automated - it needs your assistance:

    First, create a sequence and select several values:
    Code:
    SQL> create sequence my_seq start with 100;
    
    Sequence created.
    
    SQL> select my_seq.nextval from dual connect by level <= 10;
    
       NEXTVAL
    ----------
           100
           101
           102
           103
           104
           105
           106
           107
           108
           109
    
    10 rows selected.
    Now, here's where your assistance is needed - it is you who has to decide the next increment (actually, decrement) value from the sequence current value and use it in the ALTER SEQUENCE command:
    Code:
    SQL> select my_seq.currval from dual;
    
       CURRVAL
    ----------
           109
    
    SQL> alter sequence my_seq increment by -9;
    
    Sequence altered.
    In order to restart it, we'll have to select its next value:
    Code:
    SQL> select my_seq.nextval from dual;
    
       NEXTVAL
    ----------
           100
    The last step is to reset the original increment value:
    Code:
    SQL> alter sequence my_seq increment by 1;
    
    Sequence altered.
    
    SQL> select my_seq.nextval from dual connect by level <= 10;
    
       NEXTVAL
    ----------
           101
           102
           103
           104
           105
           106
           107
           108
           109
           110
    
    10 rows selected.
    Obviously, DROP and CREATE is simpler; however, think twice before you do that - will you, for example, have to GRANT access to the sequence to another user(s)? Is the sequence used to enforce uniqueness and - because of restarting - could cause unique constraint violation?

  3. #3
    Join Date
    Jul 2005
    Posts
    276
    Thanks you very much for your detailed explanation. I actually tried doing what you just said, and it works just fine. This was what i needed exactly.

  4. #4
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    LF, neat stuff.

    Also, the CYCLE clause must be of some use on these situations.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •