Sequences  in Oracle

Back to: Oracle Tutorials for Beginners and Professionals

Sequences in Oracle with Examples

In this article, I am going to discuss Sequences in Oracle with Examples. Please read our previous article where we discussed UTL_MAIL Package in Oracle.

What are Sequences in Oracle?

A sequence is a database object generating the sequence number. For example, in the Employees table, we have employee_id as the primary key. Developers make DML statements to insert records into the Employees table. Oracle recommends using the sequences in the primary key for the Employees table.

A sequence can automatically generate unique numbers. This is a shareable object.Sequence is not related to one table. The sequence can be shared across multiple tables. This can be used to create a primary key value. This replaces the application code and reduces the burden for the developers. These speed up the efficiency of accessing sequence values when cached in memory.

Syntax to Create Sequences in Oracle:

CREATE SEQUENCE schema_name.sequence_name
[INCREMENT BY interval]
[START WITH first_number]
[MAXVALUE max_value | NOMAXVALUE]
[MINVALUE min_value | NOMINVALUE]
[CYCLE | NOCYCLE]
[CACHE cache_size | NOCACHE]
[ORDER | NOORDER];

Generally, we can create the sequence by using CREATE SEQUENCE DEPT_SEQ. We have multiple options while creating sequences like INCREMENT by, start with, max value, min value, cycle, and cache. CREATE SEQUENCE [ schema].sequence_name

Sequence Clause	Description
INCREMENT BY n	Specifies the interval between sequence numbers, where n is an integer (If this clause is omitted, the sequence increments by 1.)
START WITH n	Specifies the first sequence number to be generated (If this clause is omitted, the sequence starts with 1.)
MAXVALUE n	Specifies the maximum value the sequence can generate
NOMAXVALUE	Specifies a maximum value of 10^27 for an ascending sequence and -1 for a descending sequence (This is the default option.)
MINVALUE n	Specifies the minimum sequence value.
NOMINVALUE	Specifies a minimum value of 1 for an ascending sequence and -(10^26) for a descending sequence (This is the default option.)
ORDER	Specify ORDER to guarantee that sequence numbers are generated in order of request. This clause is useful if you are using the sequence numbers as timestamps.
NOORDER	Specify NOORDER if you do not want to guarantee that sequence numbers are generated in order of request. This is the default.
CYCLE | NOCYCLE	Specifies whether the sequence continues to generate values after reaching its maximum or minimum value (NOCYCLE is the default option.)
CACHE n | NOCACHE	Specifies how many values the Oracle Server pre-allocates and keeps in memory( By default, the Oracle server caches 20 values).

The default sequence creation is to create the sequence and let Oracle choose which method is required. Let’s go ahead and learn more about the sequences by working with an exercise.

Examples to Understand Sequences in Oracle:

We will do a lot of exercises with sequences. Let’s go ahead and create a sequence DEPT_S.

CREATE STATEMENT DEPT_S;

So, the sequence DEPT_S is created. We can check the presence of a sequence from the view called USER_SEQUENCES. We can use the below query.

select * from USER_SEQUENCES where sequence_name=’DEPT_S’;

We can see the sequence_name is DEPT_S. we can see the min_value is 1 and the max_value is 28 9’s. So, we can check the length of max_value by the below query.

select length(‘9999999999999999999999999999’) from dual;

We can see the length of max_Value is 28 digits. The cycle is No. The order flag is No and the cache size is 20. This is the most information that we required for the sequences. To use the sequences, we have to create a table. So, let’s create a table DEPT_S.

CREATE TABLE DEPT_TEST_S
( DEPNO NUMBER PRIMARY KEY,
  DNAME VARCHAR2(100)
 );

So, we are creating a table DEPT_TEST_S with columns DEPNO which is the primary key, and DNAME.

So, the table is created. So, let’s insert the data into the table DEPT_TEST_S. We are going to insert DNAME into the table manually and the DEPNO will be taken by sequences.

INSERT INTO DEPT_TEST_S (DEPNO, DNAME) VALUES (DEPT_S.NEXTVAL,’Sales’);

INSERT INTO DEPT_TEST_S (DEPNO, DNAME) VALUES (DEPT_S.NEXTVAL,’Operation’);

So, we are inserting data into the table DEPT_TEST_S with column names DEPNO and DNAME. The DEPNO will be the sequence’s next value and the DNAME will be sales and operation. So, the data will be sequences in the DEPNO and the in the DNAME will be sales and operation as we are inserting two records. Let’s go ahead and insert the data.

So, the data is inserted into the table DEPT_TEST_S. Let’s check the table DEPT_TEST_S to see the data in the table.

select * from DEPT_TEST_S;

So, the data is present inside the table. We can see the DEPNO came from the sequences. We can check the current value of the sequences from the below query.

select DEPT_S.CURRVAL from dual;

So, the current value is 2. We can even check the next value of the sequences using DEPT_S.nextval from dual.

select DEPT_S.nextval from dual;

So, the next value for the sequences will be nextval. So, the value 3 is taken. If we try to insert it into the table again, then the sequence number will be 4. So, let’s try to insert another record into the table DEPT_TEST_S and see if the sequence changes.

INSERT INTO DEPT_TEST_S (DEPNO, DNAME) VALUES (DEPT_S.NEXTVAL,’IT’);

We are inserting the DNAME as IT and the DEPNO as the nextvalue of the sequences. Let’s try to execute this statement.

So, the data is inserted into the table DEPT_TEST_S. Let’s try to check the table DEPT_TEST_S.

select * from DEPT_TEST_S;

So, the record is inserted and the DEPNO is 4 as the sequence number 3 is taken by running the select query for the next value of sequences.

Note: There are many reasons that make gaps in sequences. This is one of the reasons. When we use nextval of sequences in the select statement. This indicates the next value of the sequences is taken by the select statement. Another reason is if we insert five rows in the table using the sequence.nextval and then if we make a rollback also these numbers will be taken.

Note: Do not use the cycle option if the sequence is used to generate eh primary key value. NEXTVAL & CURRVAL pseudo columns:

What are Pseudo Columns in Oracle?

This is the part of the select statement and this column is not part of the original table. The NEXTVAL returns the next available sequence value. It returns a unique value every time it is referenced, even for different users. The CURRVAL obtains the sequence value.

NEXTVAL must be issued for that sequence before CURRVAL contains a value. So, in order to retrieve the CURRVAL there should be at least one NEXTVAL.

Rules for using NEXTVAL and CURRVAL:

• The SELECT list of SELECT statements that is not part of a subquery.
• The SELECT list of a subquery in an INSERT statement.
• The VALUES clause of an INSERT statement.
• The SET clause of an UPDATE statement

NEXTVAL and CURRVAL cannot be used under the following conditions:

• A SELECT list of a view.
• A SELECT statement with the DISTINCT keyword.
• A SELECT statement with GROUP BY, HAVING, or ORDER BY clauses.
• A subquery in a SELECT, DELETE, or UPDATE statement.

Caching Sequence Values in Oracle:

Caching sequence values in memory give faster access to those values. Gaps in sequence can occur when a rollback occurs, the system crashes, or a sequence is used in another table.

Gaps in the Sequence:

Although sequence generates issue sequential numbers without gaps, this action occurs independently of a commit or rollback. Therefore, if you roll back a statement containing a sequence, the number is lost.

Another event that can cause gaps in the sequence is a system crash. If the sequence caches values in memory, those values are lost if the system crashes.

Because sequences are not tied directly to tables, the same sequence can be used for multiple tables. However, if you do so, each table can contain gaps in the sequential numbers.

Guidelines to Modify a Sequence in Oracle:

• You must be the owner or have the ALTER privilege for the sequence.
• Only future sequence numbers are affected.
• The sequence must be dropped or re-created to restart the sequence at a different number.
• You must have DROP ANY SEQUENCE privilege to remove it.
• The START WITH option cannot be changed using the ALTER SEQUENCE. The sequence must be dropped and re-created to restart the sequence at a different number.
• ORA-04009: MAXVALUE cannot be made to be less than the current value 04009.00000 – “ MAXVALUE cannot be made to be less than the current value.”.

In the next article, I am going to discuss Multiple Examples to Understand Sequences in Oracle. Here, in this article, I try to explain Sequences in Oracle with Examples. I hope you enjoy this Sequences in Oracle article.