The Complete Guide to Oracle Sequences

In this article, I’ll explain what an Oracle sequence is, how to create one, and how to use them.

What is a Sequence in Oracle?

A sequence is a database object that is used to generate unique numbers.

They are most often used for generating primary keys, as a primary key needs to be unique, and doesn’t need to be a value that’s relevant to your application or business.

Generating a unique number with a sequence is easy after it has been created.

 

Why Use a Sequence?

You might be wondering, why would I want to use a sequence?

Couldn’t I just use a table that stores the Primary Key and increment it each time? Or set a column as AUTO INCREMENT?

Well, a sequence is a great option for a few reasons.

If you’re running Oracle 12c, you can use a new feature that allows you to create an auto-incrementing column. You can read more about this here. However, this was only introduced in Oracle 12c, so if you’re on 11g or 10g or earlier, then you can’t use this feature.

The other option would be to use a table to store a primary key value. This could be done something like this:

1. Create a new table to store a single value
2. Each time you insert a new value into your desired table, use this value in the new table
3. Then, update the new table by increasing it by one
4. This logic can all go into a stored procedure if required.

However, there are several problems with this approach:

• If your transaction fails halfway through, then you could end up with duplicate data (your inserted data has an ID but the new table was not incremented). This could be partially avoided by incrementing first and then inserting, but it’s still a risk that it could fail halfway through.
• Updating a table will place a lock on it, which can cause issues for multiple transactions or users. This could be OK if there was one user, but if your application has two or more, then this can cause issues.
• It’s more work to create and maintain a table and the code to do this.

A sequence is a much better approach because:

• It’s simpler to create, as we’ll see shortly.
• It’s simpler to use
• It’s transaction safe – no risk of locking and causing issues for other transactions.

Let’s take a look at how you can create a sequence.

 

Create Sequence in Oracle

To create a sequence in Oracle, we use the CREATE SEQUENCE command.

CREATE SEQUENCE [schema.]sequence_name
[MINVALUE minval|NOMINVALUE]
[MAXVALUE maxval|NOMAXVALUE]
[START WITH startval]
[INCREMENT BY incrementval]
[CYCLE|NOCYCLE]
[CACHE cacheval|NOCACHE]
[ORDER|NOORDER];

There are a lot of parameters available, but most of them are optional. These parameters are:

• schema: The name of the schema where the sequence will be stored. If omitted, the sequence is created in your own schema.
• sequence_name: The name of the sequence to create. This is the only required parameter.
• MINVALUE minval: This is the minimum value that is generated by the sequence. It must be an integer of up to 28 digits. It must be less than or equal to START WITH and must be less than MAXVALUE.
• NOMINVAL: This indicates that there is no specified minimum value for the sequence, but uses -1026 as the minimum for descending sequences and 1 for ascending sequences. This is specified instead of the MINVALUE and is the default value (as opposed to MINVALUE).
• MAXVALUE maxval: This is the maximum value that is generated by the sequence. It must be an integer of up to 28 digits. It must be greater than or equal to START WITH and must be greater than MINVALUE.
• NOMAXVALUE: This indicates that there is no specified maximum value for the sequence, but uses 1027 as the maximum for ascending sequences and -1 for descending sequences. This is specified instead of the MAXVALUE and is the default value (as opposed to MAXVALUE).
• START WITH startval: This is the first number to be generated from the sequence. For ascending sequences, the default is the minimum value, and for descending sequences, the default is the maximum value. It can be up to 28 digits.
• INCREMENT BY incrementval: This is the interval between sequence numbers. It must be an integer, can be positive or negative, but cannot be 0. It can be up to 28 digits. The absolute value must be less than the difference between MAXVALUE and MINVALUE. If the increment value is negative, the sequence goes in descending order. If it is positive, then it goes in ascending order. If this is omitted, then the default value is 1.
• CYCLE: This indicates that the sequence keeps generating values after the MAXVALUE or MINVALUE is reached. If the sequence is ascending, and reaches the MAXVALUE, it generates the MINVALUE. If the sequence is descending, and reaches the MINVALUE, it generates the MAXVALUE.
• NOCYCLE: This indicates that the sequence can’t generate any more values after the maximum is reached. This is the default.
• CACHE cacheval: This is how many values of the sequence that the database allocates and keeps in memory to be able to access it faster. It must be greater than 2, and can be up to 28 digits. If a system failure occurs, then the cached values are lost. This is usually OK, as if they are used for primary keys, they don’t need to be sequential. The CACHE value needs to be less than the number of sequences in a cycle, if the sequence is set to cycle. The maximum for the CACHE value is:

(CEIL (MAXVALUE – MINVALUE)) / ABS(INCREMENT)

• NOCACHE: This indicates that no sequence values are allocated and cached. If you omit both CACHE and NOCACHE, then the default setting is to cache 20 sequence numbers.
• ORDER: This indicates that sequence numbers are generated in the order they are requested. This can be useful if you are using the sequence numbers for timestamps.
• NOORDER: This indicates that the sequence numbers are not guaranteed to be in the order they were requested. This is the default value.

This table indicates some scenarios using the different parameters for sequences:

MINVALUE	MAXVALUE	START WITH	INCREMENT	CYCLE	Description
n/a	n/a	1	1	No	Default. Values of 1, 2, 3, 4, 5.
10	n/a	10	1	No	Values of 10, 11, 12, 13, 14.
n/a	1000	1	5	No	Values of 1, 6, 11, 16, 21. Error caused when value over maximum reached.
10	1000	10	5	No	Values of 10, 15, 20, 25, 30. Error caused when value over maximum reached.
10	1000	10	50	Yes	Values of 10, 60, 110, 160. When maximum reached, values of 10, 60, 110 generated again.
10	500	500	-1	No	Values of 500, 499, 498, 497. Error caused when value under minimum reached.
10	500	500	-10	Yes	Values of 500, 490, 480, 470. When minimum reached, values of 500, 490, 480 generated again.

 

Create Sequence Examples

Let’s see some examples of creating sequences.

Example 1: Simple

This is an example of creating a simple sequence.

CREATE SEQUENCE seq_simple;

The sequence is created with all of the default values and can be used.

 

Example 2: MINVALUE and START WITH

This example uses both the MINVALUE and START WITH parameters.

CREATE SEQUENCE seq02
MINVALUE 10
START WITH 10;

This sequence will start at a value of 10 and generate values of 11, 12, 13, and so on.

 

Example 3: MAXVALUE and INCREMENT

This example uses both the MAXVALUE and INCREMENT parameters.

CREATE SEQUENCE seq03
MAXVALUE 1000
INCREMENT BY 5;

This sequence will start at a value of 1, and increment by 5 (generating values of 6, 11, 16, and so on), until it reaches a value over 1000. It will then cause an error.

 

Example 4: MINVALUE, MAXVALUE, START WITH, and INCREMENT

This example uses all four of these parameters.

CREATE SEQUENCE seq04
MINVALUE 10
MAXVALUE 1000
START WITH 10
INCREMENT BY 5;

This will create a sequence that generates values of 10, 15, 20, 25, and so on. Once a value over the maximum is generated, an error will occur.

 

Example 5: CYCLE

This example uses the CYCLE parameter.

CREATE SEQUENCE seq05
MINVALUE 10
MAXVALUE 1000
START WITH 10
INCREMENT BY 50
CYCLE;

This sequence will generate values of 10, 60, 110, 160, and so on. Once the maximum is reached, instead of generating an error, it will restart at the minimum value of 10, and continue with 60, 110, and so on.

 

Example 6: NOCYCLE

This sequence specifies the NOCYCLE parameter, which is the default parameter.

CREATE SEQUENCE seq06
MINVALUE 10
MAXVALUE 1000
START WITH 10
INCREMENT BY 50
NOCYCLE;

This sequence will generate values of 10, 60, 110, 160, and so on. Once the maximum is reached, an error will occur.

 

Example 7: Negative Increment

This sequence specifies a negative value for the increment.

CREATE SEQUENCE seq07
MINVALUE 10
MAXVALUE 500
START WITH 500
INCREMENT -1;

This sequence will start generating numbers of 500, 499, 498, and so on. Once it gets below the minimum, an error will occur.

 

Example 8: Negative Increment and Cycle

This example uses a negative increment value and a cycle.

CREATE SEQUENCE seq08
MINVALUE 10
MAXVALUE 500
START WITH 500
INCREMENT BY -10;

This sequence will start generating numbers of 500, 490, 480, and so on. Once it gets below the minimum, it will go back to 500, then 490, 480, and so on.

 

Example 9: CACHE

This example uses the CACHE parameter.

CREATE SEQUENCE seq09
CACHE 50;

This sequence will generate values of 1, 2, 3, and so on, but will cache the first 50 values.

 

Example 10: ORDER

This example uses the ORDER parameter.

CREATE SEQUENCE seq10
ORDER;

It will ensure sequence values are generated in order, which will become apparent when multiple users are using the system.

 

Using Sequences: How to Generate Sequence Values

So, now we’ve created a sequence to generate the values we want.

How to we generate those values?

We can use one of two pseudocolumns available for a sequence:

• CURRVAL: This gets the current value of the sequence.
• NEXTVAL: This gets the next value of the sequence and increments the sequence by its increment value.

So, to get the next value of the sequence (for example, to use in an INSERT statement to generate a primary key), you’ll need to use the NEXTVAL pseudocolumn.

INSERT INTO customer(customer_id, full_name)
VALUES (seq_simple.NEXTVAL, ‘John Smith’);

You can then query the table to see what value was inserted.

SELECT customer_id, full_name
FROM customer;

CUSTOMER_ID	FULL_NAME
1	John Smith

One thing to remember is that when calling the NEXTVAL pseudocolumn, it returns a new value for each row of data that is being selected or inserted.

For example, we can run this query to get the NEXTVAL of the sequence:

SELECT seq_simple.NEXTVAL
FROM dual;

NEXTVAL

2

Then, if I run this query with several NEXTVAL commands:

SELECT seq_simple.NEXTVAL AS nv1,
seq_simple.NEXTVAL AS nv2,
seq_simple.NEXTVAL AS nv3
FROM dual;

NV1	NV2	NV3
3	3	3

This shows that the NEXTVAL was only called once for this record, and the value is the same each time.

To find the current value of the sequence, without incrementing the number, use CURRVAL.

SELECT seq_simple.CURRVAL
FROM dual;

CURRVAL

3

These two pseudocolumns can be used in several places:

• The VALUES clause of an INSERT statement.
• The SELECT list of a SELECT statement
• The UPDATE clause of an UPDATE statement

They can’t be used in these places:

• The condition of a CHECK constraint
• A view query
• A materialised view query
• A subquery
• A SELECT statement with the DISTINCT keyword, GROUP BY clause, or ORDER BY clause
• A SELECT statement that’s combined with another SELECT statement using the UNION, INTERSECT, or MINUS operator
• The WHERE clause of a SELECT statement
• The DEFAULT value of a column in a CREATE TABLE or ALTER TABLE statement

This last point means you can’t set a column to auto increment using a sequence using the DEFAULT keyword. There is a way to create auto-increment columns, and that’s to use a trigger and a sequence, which I’ve written about here.

In Oracle 12c, a new feature was added to allow auto-increment columns to be added easier, which you can read about here.

 

Alter Sequence

To alter a sequence after it is created, use the ALTER SEQUENCE command.

You can change most properties of a sequence after it is created. The only thing you can’t change is the starting number of the sequence and the sequence’s current value. To change those, you’ll need to drop and recreate the sequence.

To use the ALTER SEQUENCE command:

ALTER SEQUENCE seq02
INCREMENT BY 20
MAXVALUE 100000
CYCLE;

This will make the above adjustments to the existing seq02 sequence. A common usage of this command is ALTER SEQUENCE INCREMENT BY, which will increment the sequence by a specified number, as we’ve seen in this example.

 

Drop Sequence

To drop a sequence, it must be in your schema, or you must have the DROP ANY SEQUENCE privilege to drop sequences in other schemas.

You can drop a sequence by using the DROP SEQUENCE command. It looks like this:

DROP SEQUENCE seq05;

This will drop the sequence seq05 from the database.

 

So, that’s how you can create and use sequences. They are handy database objects and have many advantages over the alternatives.