How to create and use an Oralce Sequence

08Apr09

This is how you can create an oracle sequence

Syntax

CREATE SEQUENCE sequence_name
MINVALUE VALUE
MAXVALUE VALUE
START WITH VALUE
INCREMENT BY VALUE
CACHE VALUE;

A Demo

CREATE SEQUENCE my_seq
MINVALUE 1
MAXVALUE 999999999999999999999999999
START WITH 1
INCREMENT BY 1

CACHE 20;
— If you don’t provide MAXVALUE its default value is 99999999999999999999999999

Using Sequence

SELECT my_seq.NEXTVAL FROM dual;

NEXTVAL
———-
1

SELECT my_seq.NEXTVAL FROM dual;

NEXTVAL
———-
2

SELECT my_seq.NEXTVAL FROM dual;

NEXTVAL
———-
3

SELECT my_seq.NEXTVAL FROM dual;

NEXTVAL
———-
4

Drop Sequence

DROP sequence my_seq;

Increment/Decrement/Alter/Reset Sequence

Now if I want to reset the sequence to a previous value, lets say my current sequence number is 4 and I want my sequence number back at 1. Here is how you can do that.

ALTER SEQUENCE my_seq INCREMENT BY -3;

SELECT my_seq.NEXTVAL FROM dual;

NEXTVAL

—————-

1

If you accendently decremented it by -4, you may get the following error

ORA-08004: sequence my_seq.NEXTVAL goes below MINVALUE and cannot be instantiated

In that case execute the following statment

ALTER SEQUENCE my_seq INCREMENT BY 4;

SELECT my_seq.NEXTVAL FROM dual;

This may solve your problem

Advertisements


2 Responses to “How to create and use an Oralce Sequence”

  1. In my table I have a column named RULE_ID.
    It currently has rows going from 1 to 30.

    but in the SEQ for it says:
    Increment by 1
    Next Value 31
    Min Value 10
    Max Value 99999
    Cache Size 0
    Cycle No
    Order No

    Q: Why does it say MinValue10 ?
    Does it analyze the table to get that?
    Shouldn’t it be 1?

    Thanks for any clues!
    -cellurl

    • 2 meter down

      I can’t say for sure other than that the value at the time of executing the sequence was provided as 10 for Min Value. But you can alter your sequence and assign some other value like 1 to the Min Value attribute like this

      ALTER SEQUENCE my_seq MINVALUE 1;

      But if you try to set a value greater than the current MINVALUE then you will get this error. So MINVALUE would always be less than or equal to the current MINVALUE once you decide to alter the MINVALUE of a sequence .


      MINVALUE cannot be made to exceed the current value

      Cause: the given MINVALUE would be greater than the current value

      Action: always make sure that MINVALUE is <= the current value


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: