Oracle SQL Sequence Creation and Auto-Numbering Basics [CREATE SEQUENCE]

An Oracle Sequence (SEQUENCE) is a database object used to automatically generate unique sequential numbers. It is primarily used to efficiently assign non-duplicate IDs for table Primary Keys.

This article explains how to create sequences using the CREATE SEQUENCE statement, implement auto-numbering using NEXTVAL, and handle operational considerations such as “gaps” and “reset methods,” assuming Oracle Database 19c for beginners.

What you will learn in this article

The basic mechanism and creation steps of an Oracle Sequence.
Auto-numbering in SQL using NEXTVAL / CURRVAL.
Why gaps occur in sequential numbers and the correct methods to reset them.

Conclusion: The Shortest Steps to Use a Sequence

The “To-Do List” to implement auto-numbering is as follows:

Create: Create a sequence object using CREATE SEQUENCE.
Check: Verify settings using the USER_SEQUENCES view.
Use: Call sequence_name.NEXTVAL in an INSERT statement (or similar).

What is a Sequence (SEQUENCE)?

A sequence is a mechanism like a counter managed in memory that handles the logic: “The last number issued was X, so the next one to pass is X+1.”

Key Benefits

Auto-numbering: Eliminates the need for the application to query the “current maximum ID” and add 1.
High Speed & High Concurrency: Even if multiple transactions access it simultaneously, it can issue numbers with minimal lock contention (managed within the SGA, Oracle Database’s memory structure).
Independence: Since it does not depend on a specific table, a single sequence can be shared across multiple tables.

Note: In Oracle Database 12c and later, “Identity Columns” (defined with GENERATED AS IDENTITY in the table column definition) are also available. These use sequences internally but allow for more concise definitions. This article explains the fundamental sequence object itself.

Step 1: Creating a Sequence (CREATE SEQUENCE)

Here is the explanation of the basic syntax and major options.

Basic Syntax

CREATE SEQUENCE sequence_name
  [START WITH n]
  [INCREMENT BY n]
  [MAXVALUE n | NOMAXVALUE]
  [MINVALUE n | NOMINVALUE]
  [CYCLE | NOCYCLE]
  [CACHE n | NOCACHE];

Parameter Explanation

Parameter	Description	Recommended Settings / Notes
START WITH	The initial value to start numbering. Default is 1.	If existing data is present, specify `MAX(ID) + 1`.
INCREMENT BY	The increment value. If 1, it increases as 1, 2, 3…	Specify a negative value for a decreasing sequence.
CACHE	The number of values pre-allocated in memory.	Important: Default is 20. Consider 100 or more for high-load environments to improve performance. However, cached numbers are lost (causing gaps) during an instance failure (detailed later).
CYCLE	Whether to return to the initial value after reaching the maximum.	`NOCYCLE` (default) is recommended for standard Primary Key usage.

Step 2: Practical Creation and Usage Examples

The following examples can be executed in SQL*Plus or SQL Developer. Here, we will auto-generate IDs for an employee master table (EMPLOYEES).

Prerequisites

Connection User: General user (e.g., SCOTT) or DBA.
Required Privileges: CREATE SEQUENCE, CREATE TABLE.

1. Creating the Sequence and Table

First, create the sequence. Here, we configure it to start from 1 and increment by 1.

-- Create sequence
CREATE SEQUENCE seq_emp_id
  START WITH 1
  INCREMENT BY 1
  NOCYCLE
  NOCACHE;

-- Create table
CREATE TABLE employees (
    id   NUMBER PRIMARY KEY,
    name VARCHAR2(50)
);

Explanation: Specifying NOCACHE updates the data dictionary every time a number is retrieved, ensuring high reliability. However, this may impact performance in systems with massive INSERT operations (usually, the default CACHE 20 is fine).

2. Inserting Data with NEXTVAL

Specify sequence_name.NEXTVAL in the VALUES clause of the INSERT statement.

-- 1st insertion (1 is automatically assigned)
INSERT INTO employees (id, name)
VALUES (seq_emp_id.NEXTVAL, 'John Doe');

-- 2nd insertion (2 is automatically assigned)
INSERT INTO employees (id, name)
VALUES (seq_emp_id.NEXTVAL, 'Jane Smith');

-- Commit to finalize
COMMIT;

3. Checking Results

Check the table data and the current value of the sequence.

-- Check data
SELECT * FROM employees;

-- Check current sequence value (CURRVAL is available only after NEXTVAL is executed in the session)
SELECT seq_emp_id.CURRVAL FROM dual;

Execution Result Image:

SQL> CREATE SEQUENCE seq_emp_id
  2    START WITH 1
  3    INCREMENT BY 1
  4    NOCYCLE
  5    NOCACHE;

Sequence created.

SQL> CREATE TABLE employees (
  2      id   NUMBER PRIMARY KEY,
  3      name VARCHAR2(50)
  4  );

Table created.

SQL> INSERT INTO employees (id, name)
  2  VALUES (seq_emp_id.NEXTVAL, 'John Doe');

1 row created.

SQL> INSERT INTO employees (id, name)
  2  VALUES (seq_emp_id.NEXTVAL, 'Jane Smith');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM employees;

        ID NAME
---------- --------------------------------------------------
     ★  1 John Doe
     ★  2 Jane Smith

SQL> SELECT seq_emp_id.CURRVAL FROM dual;

   CURRVAL
----------
         2

Operations & Troubleshooting

Here are common questions, troubles, and solutions when operating sequences.

Common Errors and Solutions

ORA Error	Cause	Solution / Check
ORA-08002: sequence … is not yet defined in this session	`CURRVAL` was referenced without executing `NEXTVAL` at least once in that session.	Run `NEXTVAL` first, or if checking is the goal, query `LAST_NUMBER` in the `USER_SEQUENCES` view.
ORA-02289: sequence does not exist	The sequence name is incorrect, or privileges are missing.	Check spelling, or verify privileges via `GRANT SELECT ON sequence_name TO user`.

About Gaps

Sequences do not guarantee a “perfectly contiguous series.” Gaps will occur in the following cases. It is necessary to understand this as part of the specification.

Rollback: If you INSERT using a number obtained via NEXTVAL and then rollback the transaction, the sequence counter does not return to the previous value.
Cache Loss: If CACHE is set (default 20), unused numbers in memory are discarded during a DB restart or crash. Upon restart, numbering skips those values.

How to Reset (Initialize) a Sequence

There are cases, such as after testing is complete, where you want to return the sequence number to 1.

Method A: Drop and Recreate (Recommended / Certain)

This is the simplest method, but privileges (GRANT) on the sequence will be lost, so they must be re-granted.

-- Delete
DROP SEQUENCE seq_emp_id;

-- Recreate (Don't forget to grant privileges)
CREATE SEQUENCE seq_emp_id START WITH 1 ...;

Method B: Change INCREMENT BY (Advanced / For Live Operations)

This is a technique to use when you want to maintain the Object ID and privileges.

Check the current value.
Use ALTER SEQUENCE to set the increment to a negative value.
Execute NEXTVAL to lower the value.
Return the increment to 1.

-- Example: Current value is 1000 and you want to return to 1
ALTER SEQUENCE seq_emp_id INCREMENT BY -999 MINVALUE 0;

SELECT seq_emp_id.NEXTVAL FROM dual; -- This sets it to 1

ALTER SEQUENCE seq_emp_id INCREMENT BY 1;

FAQ: Frequently Asked Questions

Q1. What is CACHE? What are the benefits?

It is a feature that pre-allocates a set of sequence values in memory. The default is 20. It reduces the number of writes to the disk, improving performance in systems with high volumes of simultaneous access. However, the pre-allocated block becomes a gap upon DB restart.

Q2. Is there a way to absolutely prevent gaps?

There is no way to completely prevent them using only the sequence function. The NOCACHE option reduces gaps caused by caching, but gaps caused by rollbacks cannot be prevented. If a completely contiguous series is legally required (e.g., for tax documents), you must not use sequences but instead prepare a separate numbering table and manage it with exclusive control (locking) (note that performance will degrade).

Q3. How do I check sequence settings in a production environment?

Refer to the USER_SEQUENCES view (owned by you) or ALL_SEQUENCES view (access rights available).

SELECT sequence_name, min_value, max_value, increment_by, cache_size, last_number
FROM user_sequences
WHERE sequence_name = 'SEQ_EMP_ID';

Note: When using cache, last_number indicates the “next number to be allocated to memory,” so it may display a value larger than the number currently being issued.

Summary

Auto-numbering: Easily generate unique IDs with CREATE SEQUENCE and NEXTVAL.
Configuration: Specify the start value with START WITH and the interval with INCREMENT BY.
Note: Design with the premise that gaps will occur due to rollbacks and caching mechanisms.
Reset: Ideally recreate (DROP & CREATE), but be careful of privilege loss.

Sequences are a fundamental part of database design. Understand their characteristics to aid in efficient data management.

Note: This article explains concepts based on Oracle Database 19c. Defaults and behaviors may differ depending on the version.

[reference]
Oracle Database SQL Language Reference, 19c