How do you refresh a sequence?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: How do you refresh a sequence?

  1. #1
    Join Date
    Mar 2001
    Posts
    286
    How do you refresh (reset) a sequence so that it start from the beginning?

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    you have to change your increment interval to negative then sleect the nextval from dual, this will decrease your sequence number, after that change the increment again to the normal interval

    in the worst of the case (if you dont know how to alter the interval) recreate it

  3. #3
    Join Date
    Jun 2000
    Location
    dumfries,va,usa
    Posts
    227
    You cannot. You have to drop the sequence and recreate from where you would like it to start. All other parameters can be used in the alter sequence except for Start with.
    leonard905
    leonard905@yahoo.com

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    we dont change start with value, we change minvalue value

    Code:
    SQL> create sequence seq
      2  start with 1
      3  increment by 1
      4  nocache
      5  nocycle
      6  minvalue 1;
    
    Sequence created.
    
    SQL> select seq.nextval from dual;
    
       NEXTVAL
    ----------
             1
    
    SQL> r
      1* select seq.nextval from dual
    
       NEXTVAL
    ----------
             2
    
    SQL> r
      1* select seq.nextval from dual
    
       NEXTVAL
    ----------
             3
    
    SQL> alter sequence seq minvalue 0 increment by -3
    
    Sequence altered.
    
    SQL> select seq.nextval from dual;
    
       NEXTVAL
    ----------
             0
    
    SQL> alter sequence seq increment by 1;
    
    Sequence altered.
    
    SQL> select seq.nextval from dual;
    
       NEXTVAL
    ----------
             1
    
    Back to business
    
    
    
    SQL> select seq.nextval from dual;
    
       NEXTVAL
    ----------
             2
    
    SQL> r
      1* select seq.nextval from dual
    
       NEXTVAL
    ----------
             3

  5. #5
    Join Date
    Mar 2001
    Posts
    286
    Pando is correct and excellent!

  6. #6
    Join Date
    Oct 2002
    Posts
    391
    Hi.

    I am on 9.2.0.5. I have the problem as belows

    SQL> select seq.nextval from dual
    2 ;
    select seq.nextval from dual
    *
    ERROR at line 1:
    ORA-02289: sequence does not exist

    Please advise.

  7. #7
    Join Date
    Aug 2000
    Location
    Jolo, Sulu
    Posts
    639
    your sequence named "seq" is not there....try
    sql> create sequence seq;

    Ms Congeniality III

  8. #8
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    Originally posted by yls177
    Hi.

    I am on 9.2.0.5. I have the problem as belows

    SQL> select seq.nextval from dual
    2 ;
    select seq.nextval from dual
    *
    ERROR at line 1:
    ORA-02289: sequence does not exist

    Please advise.
    check who's the owner of the seq.
    you might need to create synonym.

Posting Permissions

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


Click Here to Expand Forum to Full Width