13.1 Sequences

13.1 Sequences

In relational database systems, tables frequently require synthetic unique identifiers known as surrogate primary keys (such as customer_id, order_id, or invoice_number). Rather than requiring application logic to lock tables or calculate MAX(id) + 1 (which introduces severe concurrency bottlenecks and race conditions), Oracle Database provides Sequences.

A Sequence is an independent schema object stored in the Oracle data dictionary (USER_SEQUENCES) that automatically generates unique, sequential numeric integers. A sequence is not tied to any single table or column; multiple tables can share a single sequence, or a single table can draw from multiple sequences.

---

Architecture and Memory Management of Sequences

To provide high-throughput integer generation without disk I/O bottlenecks, Oracle Database manages sequence values through the System Global Area (SGA) shared memory cache.

+-------------------------------------------------------------------------+
|                       ORACLE SEQUENCE ARCHITECTURE                      |
+-------------------------------------------------------------------------+
|                                                                         |
|  USER SESSION A:                   USER SESSION B:                      |
|  INSERT INTO orders                INSERT INTO orders                   |
|  VALUES (ord_seq.NEXTVAL, ...);    VALUES (ord_seq.NEXTVAL, ...);       |
|              |                                 |                        |
|              +----------------+----------------+                        |
|                               |                                         |
|                               v                                         |
|  SGA (SHARED POOL / SEQUENCE CACHE):                                    |
|  [ Cached Block of Sequence Integers: 101, 102, 103, ... 120 ]          |
|  - Provides instantaneous, lock-free allocation in RAM                  |
|  - When cache is exhausted, next batch is allocated from disk           |
|                               |                                         |
|                               v (Only upon cache refresh or DDL)        |
|  DATA DICTIONARY STORAGE (SYSTEM TABLESPACE / USER_SEQUENCES):          |
|  [ Sequence Definition: START WITH, INCREMENT BY, LAST_NUMBER, CACHE ]  |
|                                                                         |
+-------------------------------------------------------------------------+

---

The CREATE SEQUENCE Statement & Parameter Reference

Creating a sequence in Oracle SQL uses the CREATE SEQUENCE DDL statement. All parameter clauses are optional and carry pre-defined default behaviors.

Syntax

CREATE SEQUENCE [schema.]sequence_name
    [INCREMENT BY integer]
    [START WITH integer]
    [MAXVALUE integer | NOMAXVALUE]
    [MINVALUE integer | NOMINVALUE]
    [CYCLE | NOCYCLE]
    [CACHE integer | NOCACHE]
    [ORDER | NOORDER];

Detailed Parameter Reference Table

Creation Example

-- Create an ascending sequence for generating customer IDs
CREATE SEQUENCE customer_id_seq
    START WITH 1000
    INCREMENT BY 5
    MAXVALUE 999999
    NOCYCLE
    CACHE 25;

---

Pseudo-Columns: NEXTVAL and CURRVAL

Oracle provides two specialized pseudo-columns to interact with sequence objects: NEXTVAL and CURRVAL.

1. NEXTVAL Mechanics

Referencing sequence_name.NEXTVAL advances the sequence according to its INCREMENT BY setting and returns the newly generated integer value.

-- Generate and retrieve the next sequence number
SELECT customer_id_seq.NEXTVAL FROM dual;
-- Returns: 1000 (first invocation returns START WITH value)

SELECT customer_id_seq.NEXTVAL FROM dual;
-- Returns: 1005 (incremented by 5)

2. CURRVAL Mechanics & The ORA-08002 Error

Referencing sequence_name.CURRVAL returns the current sequence value previously generated in the current user session without incrementing the sequence.

-- Read current value without advancing the sequence
SELECT customer_id_seq.CURRVAL FROM dual;
-- Returns: 1005

[!CRITICAL] The ORA-08002 Session Rule: In any individual database session, you cannot reference CURRVAL until NEXTVAL has been referenced at least once for that sequence in that specific session. If a new session connects and immediately executes SELECT my_seq.CURRVAL FROM dual;, Oracle halts execution and raises: ORA-08002: sequence MY_SEQ.CURRVAL is not yet defined in this session

3. Multiple NEXTVAL References in a Single SQL Statement

If a single SQL statement references NEXTVAL multiple times for the same sequence within the same row processing step, Oracle increments the sequence only once for that row, and all references in that row return the identical integer value.

-- Both columns receive the EXACT same number; sequence increments only by 1
INSERT INTO order_audit (order_id, tracking_id, order_date)
VALUES (order_seq.NEXTVAL, order_seq.NEXTVAL, SYSDATE);

---

Valid vs. Invalid Contexts for Pseudo-Columns (ORA-02287)

Understanding where NEXTVAL and CURRVAL can and cannot appear in SQL statements is one of the most heavily tested topics on the 1Z0-071 exam.

+-------------------------------------------------------------------------+
|                 SEQUENCE PSEUDO-COLUMN CONTEXT MATRIX                   |
+-------------------------------------------------------------------------+
|                                                                         |
|  VALID CONTEXTS (Allowed):                                              |
|  [✓] Top-level SELECT list of a query (not in disallowed subquery)      |
|  [✓] VALUES clause of an INSERT statement                               |
|  [✓] SELECT list of an INSERT ... SELECT statement                      |
|  [✓] SET clause of an UPDATE statement                                  |
|                                                                         |
|  INVALID CONTEXTS (Raises ORA-02287):                                   |
|  [X] WHERE clause of SELECT, UPDATE, or DELETE                          |
|  [X] GROUP BY or HAVING clauses                                         |
|  [X] ORDER BY clause                                                    |
|  [X] SELECT query containing the DISTINCT or UNIQUE operator            |
|  [X] Queries combined using Set Operators (UNION, INTERSECT, MINUS)    |
|  [X] Subqueries in SELECT, UPDATE, or DELETE                            |
|  [X] View definitions (AS subquery in CREATE VIEW)                      |
|  [X] Column DEFAULT declarations in CREATE TABLE (prior to 12c)         |
|  [X] CHECK constraint definitions                                       |
|                                                                         |
+-------------------------------------------------------------------------+

Examples of Valid Usage

-- 1. In the VALUES clause of an INSERT statement
INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, job_id, salary)
VALUES (employees_seq.NEXTVAL, 'Sarah', 'Connor', 'SCONNOR', SYSDATE, 'IT_PROG', 8500);

-- 2. In the SET clause of an UPDATE statement
UPDATE customer_orders
SET invoice_number = invoice_seq.NEXTVAL
WHERE order_status = 'PENDING_INVOICE';

-- 3. In the SELECT list of an INSERT ... SELECT statement
INSERT INTO archive_orders (archive_id, order_id, order_date)
SELECT archive_seq.NEXTVAL, order_id, order_date
FROM orders
WHERE order_date < DATE '2025-01-01';

Examples of Illegal Usage and ORA-02287

-- ILLEGAL: Sequence in WHERE clause
SELECT employee_id, last_name
FROM employees
WHERE employee_id = emp_seq.CURRVAL; -- ORA-02287: sequence number not allowed here

-- ILLEGAL: Sequence with DISTINCT operator
SELECT DISTINCT dept_seq.NEXTVAL, department_id
FROM employees; -- ORA-02287: sequence number not allowed here

-- ILLEGAL: Sequence in GROUP BY or ORDER BY
SELECT department_id, count(*)
FROM employees
GROUP BY department_id, emp_seq.NEXTVAL; -- ORA-02287: sequence number not allowed here

---

Causes of Sequence Number Gaps

A sequence guarantees that every generated integer is unique; it does NOT guarantee contiguity (gap-free numbers). In production database systems, sequence gaps are normal and expected.

Primary Root Causes of Sequence Gaps

1. Transaction Rollbacks: When an INSERT statement calls NEXTVAL, the sequence advances immediately. If the transaction is subsequently rolled back (ROLLBACK), the generated sequence number is permanently lost and is never reused or returned to the pool.
2. Instance Failure / System Crashes: Sequence numbers pre-allocated in the SGA cache (CACHE 20) are stored in volatile RAM. If the database instance crashes or is shut down via SHUTDOWN ABORT, all unused cached sequence values are destroyed. When the instance restarts, Oracle loads the next batch starting from LAST_NUMBER in the data dictionary.
3. Multiple Tables Sharing a Sequence: If Table A and Table B share global_id_seq, Table A might receive IDs 101, 103, and 104, while Table B receives ID 102. To each individual table, gaps appear in their numbering sequence.
4. Uncommitted or Failed DML: If an INSERT statement fails due to a CHECK constraint or UNIQUE key violation after evaluating NEXTVAL, the allocated number is discarded.

---

Modifying Sequences with ALTER SEQUENCE

You can modify the operational characteristics of an existing sequence using ALTER SEQUENCE.

Syntax

ALTER SEQUENCE [schema.]sequence_name
    [INCREMENT BY integer]
    [MAXVALUE integer | NOMAXVALUE]
    [MINVALUE integer | NOMINVALUE]
    [CYCLE | NOCYCLE]
    [CACHE integer | NOCACHE]
    [ORDER | NOORDER];

Critical Rules and Prohibitions for ALTER SEQUENCE

1. Cannot Modify START WITH (ORA-02283): The starting value (START WITH) cannot be altered using ALTER SEQUENCE. To restart or reset a sequence's starting integer, you must drop the sequence (DROP SEQUENCE seq_name;) and recreate it (CREATE SEQUENCE seq_name START WITH n;).

   -- ILLEGAL: Attempting to alter START WITH
   ALTER SEQUENCE customer_id_seq START WITH 5000;
   -- ORA-02283: cannot alter starting sequence number
   

2. Future Value Impact Only: Altering an INCREMENT BY, MAXVALUE, or CACHE setting affects only subsequently generated numbers; it does not retroactively modify numbers that were already generated.
3. Validation of New Limits: If you change MAXVALUE or MINVALUE, the new MAXVALUE cannot be less than the current sequence value (LAST_NUMBER).

Modifying Example

-- Increase increment step to 10 and expand cache to 50
ALTER SEQUENCE customer_id_seq
    INCREMENT BY 10
    CACHE 50;

---

Dropping Sequences & Data Dictionary Inspection

A sequence is removed from the database using the DROP SEQUENCE statement:

DROP SEQUENCE customer_id_seq;

Data Dictionary Views for Sequences

Sequence metadata is monitored via USER_SEQUENCES and ALL_SEQUENCES:

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

Exam Tip: In USER_SEQUENCES, the LAST_NUMBER column displays the next sequence value that will be written to disk if NOCACHE is used, or the next starting value of the next cache block if CACHE is enabled.