This discussion is archived
8 Replies Latest reply: May 21, 2009 10:31 AM by WayneFMcKinstry RSS

change the nextval for a sequence

WayneFMcKinstry Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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
    481319 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
      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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Incrementing until it was where it needed to be worked beautifully. Thanks

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points