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

Filed under: Oracle, Tutorials   |  2 Comments
Tags: Increment and decrement an oracle sequence, Oracle, Oracle sequence