8 Replies Latest reply: May 21, 2009 12:31 PM by WayneFMcKinstry RSS

    change the nextval for a sequence

    WayneFMcKinstry
      I need to change the nextval for a sequence. What is the code to do this?

      Thanks, Wayne
        • 1. Re: change the nextval for a sequence
          Peter Gjelstrup
          Hi,

          Drop Sequence ...
          Create Sequence ... START WITH ...
          (But, don't forget about existing privileges, if any!)


          Or

          Select nextval as many times needed until you reach the value you need
          maybe changing the INCREMENT BY, first

          Regards
          Peter

          Edited by: Peter on May 21, 2009 9:13 AM
          - Added comment on privs
          • 2. Re: change the nextval for a sequence
            Christian Balz
            Hi Wayne,

            try this,
            alter sequence YOUR_SEQ increment by &new_value-&actual_position  nocache;
            select YOUR_SEQ.nextval from dual;
            alter sequence YOUR_SEQ increment by 1 nocache;
            declare
              LastValue integer;
            begin
              loop
                select YOUR_SEQ.currval into LastValue from dual;
                exit when LastValue >= &new_value - 1;
                select YOUR_SEQ.nextval into LastValue from dual;
              end loop;
            end;
            /
            alter sequence YOUR_SEQ increment by 1 cache 20;
            Regards,
            Christian Balz

            Edited by: Christian Balz on 21/05/2009 09:09

            Edited by: Christian Balz on 21/05/2009 09:14
            • 3. Re: change the nextval for a sequence
              SanjayRs
              Drop and create will drop the grants given .

              To reset the sequence backwards use -1 to go backwards.
              var x_n number ; 
              alter sequence SEQ_one increment by  -1 ;
              begin 
              select SEQ_one.nextval into :x_n from dual ;
              while ( :x_n > 1 ) 
              Loop 
              select SEQ_one.nextval into :x_n from dual ;
              end loop ;
              exception when others then 
               if SQLCODE=-8004 then null ;
               else raise ; 
               end if ;
              end ;
              /
              alter sequence SEQ_one increment by  1 ;
              var last number ;
              begin
               select the_last_number into :last from dual ;
               for i in 1..:last loop
                select SEQ_one.nextval into :x_n from dual ;
               end loop ;
              end ;
              /
              !http://download.oracle.com/docs/cd/B14117_01/server.101/b10759/alter_sequence.gif!
              • 4. Re: change the nextval for a sequence
                Peter Gjelstrup
                As can be seen, NEXTVAL isn't mentioned.

                So why post that?

                Edit:
                (I do know, that you can change INCREMENT BY and then select NEXTVAL only once, to get what you want)

                Regards
                Peter

                Edited by: Peter on May 21, 2009 9:09 AM
                • 5. Re: change the nextval for a sequence
                  Frank Kulash
                  Hi, Wayne,

                  As an example of what Peter meant by "changing the INCREMENT BY":
                  Say seq_x has just generated the numbers ..., 98, 99 and 100.
                  Now you want to reset it, so that it will generate 1, 2, 3, ...
                  ALTER SEQUENCE seq_x INCREMENT BY -99;
                  SELECT seq_x.NEXTVAL FROM dual;
                  will produce 1.
                  Following that
                  ALTER SEQUENCE seq_x INCREMENT BY 1;
                  SELECT seq_x.NEXTVAL FROM dual;
                  SELECT seq_x.NEXTVAL FROM dual;
                  will produce 2 and 3.

                  Make sure no one else is using the sequence while you do this.
                  • 6. Re: change the nextval for a sequence
                    brtk
                      alter sequence seq increment by (desired_value-current_value);
                      select seq.nextval from dual;
                      alter sequence seq increment by (old_increment);
                    Bartek
                    • 7. Re: change the nextval for a sequence
                      Peter Gjelstrup
                      Hi Frank,

                      Thanks for elaborating. That's a good example, especially since it's using negative increment.

                      Regards
                      Peter
                      • 8. Re: change the nextval for a sequence
                        WayneFMcKinstry
                        Incrementing until it was where it needed to be worked beautifully. Thanks