Results 1 to 2 of 2

Thread: Alter Sequence

  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: Alter Sequence

    I was altering a sequence as follows:

    ALTER SEQUENCE MY_SEQ
    INCREMENT BY 1
    MINVALUE 100000
    NOMAXVALUE
    NOCACHE
    NOCYCLE
    NOORDER;

    currval is 1. I get the error:

    ERROR at line 1:
    ORA-04007: MINVALUE cannot be made to exceed the current value


    I looked up sequences in the manual, and I see there's an edit on MAXVALUE (can't be set to a value < currval), but I didn't see anything on MINVALUE.

    Conpetually, I do not understand why the MINVALUE couldn't be set greater greater than currval.

    -Chuck

  2. #2
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249
    If you specify that the minvalue is greater than the current value, there arises a contradiction.


    Let us say that the current value is 100. You want to set the minvalue to 200.
    The sequence has to start counting from its current value, that is, 100. What should the value be when the sequence is used?

    Based on the current value, it should be 101. But that will be less than 200, the minimum value you specified.

    Therefore, Oracle DBMS tells you it can not be done.

    Since there is essentially zero overhead for sequences, just drop the sequence and recreate it.

    Or, write a script to keep getting the next value of the sequence until the current value reaches your desired value.

    Ravi

Posting Permissions

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