Sequence in Oracle with Examples

What is a Sequence?

A Sequence is an Oracle Database Object that we create to generate numeric(integer) values.

Often while working with Tables in Oracle database with Primary key constraint, we need to insert a new row into a table and manually input the primary_key value by incrementing the previous value in series. This can get tedious when working with big tables with huge datasets.

Sequence is an object which automatically checks the previous primary key value and increments it by a value set by us, when inserting a new row.

In this tutorial we will learn how to use, create, alter and drop a sequence object in Oracle.

Create a dummy table for Examples

Let’s create a dummy table named ‘Managers’ for these examples and insert some values into it:

CREATE TABLE managers
(
manager_id NUMBER(10) PRIMARY KEY,
first_name VARCHAR(10),
last_name VARCHAR(10)
)

INSERT INTO managers VALUES(101, 'John',
'Snow');
INSERT INTO managers VALUES(102, 'Khal',
'Drogo');
INSERT INTO managers VALUES(103, 'Sansa',
'Stark');

As we can see, all the values in manager_id column are getting incremented by one. Instead of manually checking the previous primary key value and incrementing it by one, we will create a sequence object to do it automatically.

Create Sequence

The syntax for creating a Sequence is as follows:

CREATE SEQUENCE schema_name.sequence_name
  INCREMENT BY value
  START WITH value
  MINVALUE value
  MAXVALUE value
  [CYCLE / NOCYCLE]
  [CACHE cache_size /NOCACHE];

Here,

schema_name – Optional. It is the name of schema in which we want to create sequence.

sequence_name – It is the name of sequence object which must be unique.

INCREMENT BY – It is the numeric value by which we want the sequence to be incremented each time. The default value of increment is 1. We can enter both positive and negative values here which will result in ascending or descending sequence of numbers.

START WITH – It is the first number from which we want the sequence to start.

MAXVALUE – Specify the maximum value that can be generated by the sequence.

MINVALUE -Specify the minimum value of the sequence.

CYCLE – We use CYCLE to generate a value after the max or min value has been reached. It generates max value for an ascending sequences and vice versa.

NOCYCLE – For stopping the sequence from generating new value once the limit has been reached.

CACHE – We can cache the sequence values and keep it in temporary memory for faster access.

Let’s create a new sequence object named test_seq to generate numeric values.

CREATE SEQUENCE test_seq
INCREMENT BY 1
START WITH 101
MAXVALUE 999
CACHE 50
NOCYCLE;

Here, each generated value gets incremented by 1 and the starting number for sequence is 101 while the maximum value is 999, beyond which no new sequence number will be generated. We use cache = 50 to cache 50 values for better performance. If the table is small, we can simply use NOCACHE also.

Now, that the sequence object named ‘test_seq’ has been created, we need to keep these two pseudonyms in mind for using the sequence object in query.

NEXTVAL – For generating next value in sequence

CURRVAL – For showing the current value of sequence number.

The following query shows the next generated value by sequence object:

SELECT test_seq.NEXTVAL FROM DUAL

Result:

Each time, we execute the query the value gets incremented by our increment value, in this case 1.

The following query shows the current value of sequence:

SELECT test_seq.CURRVAL FROM DUAL

If we don’t execute the NEXTVAL query again, then the value of CURRVAL would remain the same. After executing the NEXTVAL query 2 more times, the CURRVAL becomes = 103

Result:

Now, lets use this sequence object for generating sequential values for our primary key column in ‘Managers’ table:

INSERT INTO managers VALUES(test_seq.NEXTVAL, 'Arya',
'Stark');
INSERT INTO managers VALUES(test_seq.NEXTVAL, 'Tyrion',
'Lannister');

Let’s see the managers table after executing above two queries:

ALTER Sequence Example

The syntax for altering the created sequence is as follows:

ALTER SEQUENCE sequence_name
    [INCREMENT BY interval]
    [MAXVALUE max_value / NOMAXVALUE]
    [MINVALUE min_value / NOMINVALUE]
    [CYCLE / NOCYCLE]
    [CACHE cache_size / NOCACHE]
    [ORDER / NOORDER];

Here, all the keywords are same as above and we can make changes to the existing sequence object using ALTER statement. Sometimes Oracle might issue an error while altering the sequence and we need to drop the existing sequence and recreate it.

Here, we are altering the ‘test_seq’ by changing the increment_value to 10 and MAXVALUE to 1000.

ALTER SEQUENCE test_seq
INCREMENT BY 10
--START WITH 10
MAXVALUE 1000
NOCACHE
NOCYCLE;

When we add one more row into the managers table, the next sequence will be previous number + 10, instead of previous + 1.

DROP Sequence Example

The syntax for dropping the sequence object is as follows:

DROP SEQUENCE schema_name.sequence_name;

Let’s drop the test sequence we just created.

DROP SEQUENCE test_seq;

Note: After deleting the sequence object, the generated sequence numbers in table won’t be deleted.

See all system sequences

If we want to see all the sequences in the current schema then we use the following query:

It gives us all the meta data related to the sequence object that are present in user schema.

SELECT * FROM user_sequences;

If we want to see a list of all the sequences present in Oracle database then we can use this query:

SELECT * FROM all_sequences;

In this tutorial, we learned how to use, create, alter and drop sequences in Oracle.