Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2003
    Posts
    3

    Unanswered: reset a sequence?

    Is there an easy way to reset a sequence to a specific value? I know I could drop and create, then hit it x number of times, but is there a nicer way?

  2. #2
    Join Date
    May 2003
    Location
    Dublin, Ireland
    Posts
    44

    Re: reset a sequence?

    You can create it with start value then you dont need to 'hit it x times'.;-)
    But you can use following that changes sequence number to

    -- get durrent value
    col IncVaL noprint new_value uIncVaL
    select -1 - test_seq.nextval IncVaL from dual;

    -- get increment
    col ByVal noprint new_value uByVal
    select increment_by ByVal
    from dba_sequences
    where sequence_name = 'TEST_SEQ';

    -- display current value
    select test_seq.nextval "OLD SEQUENCE VALUE" from dual;

    -- change increment
    alter sequence test_seq
    increment by &uIncVaL
    minvalue 0
    /

    -- reset sequence
    select test_seq.nextval "RESETTING SEQUENCE VALUE" from dual;

    -- set increment
    alter sequence test_seq increment by &uByVal;

    -- display new sequence value
    -- that the next access will return a 1
    select test_seq.nextval "NEW SEQUENCE VALUE " from dual;

    Cheers,

    Jacek

    Originally posted by joj
    Is there an easy way to reset a sequence to a specific value? I know I could drop and create, then hit it x number of times, but is there a nicer way?

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: reset a sequence?

    Originally posted by joj
    Is there an easy way to reset a sequence to a specific value? I know I could drop and create, then hit it x number of times, but is there a nicer way?
    You could do it like this:

    1) Obtain current value of sequence, e.g. <y>

    2) SQL> alter sequence <seqname> maxvalue <y> minvalue <x> cycle;

    3) Repeat "select <seqname>.nextval from dual;" until value cycles to <x>

    4) Restore sequence properties, e.g.

    SQL> alter sequence <seqname> nomaxvalue nominvalue nocycle;

Posting Permissions

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