13.1 Sequences

Oracle Database SQL Certified Associate 1Z0-071

Track your progress as you read

Finished sections get a check mark.

1 Chapter 1: Relational Database Concepts & Logical Modeling

1.1 Relational Database Architecture & Terminology 1.2 Entity Relationship Modeling & SQL Mapping 1.3 Database Normalization & Design Fundamentals

2 Chapter 2: Retrieving Data with SQL SELECT

2.1 SQL SELECT Fundamentals & Projection 2.2 Literal Values, Concatenation, and Quote Operators 2.3 Handling NULLs & Distinct Rows in Projection

3 Chapter 3: Restricting and Sorting Query Data

3.1 WHERE Clause Filtering & Comparison Operators 3.2 Logical Conditions & Operator Precedence 3.3 Sorting Data with ORDER BY 3.4 Substitution Variables & Session Directives

4 Chapter 4: Customizing Output with Single-Row Functions

4.1 Character Manipulation & Case Conversion 4.2 Numeric & Mathematical Functions 4.3 Date Arithmetic & Date Manipulation Functions 4.4 Nesting Single-Row Functions

5 Chapter 5: Conversion Functions and Conditional Expressions

5.1 Implicit vs Explicit Datatype Conversion 5.2 TO_CHAR, TO_NUMBER, and TO_DATE Functions 5.3 General NULL-Handling Functions 5.4 Conditional Expressions: CASE and DECODE

6 Chapter 6: Reporting Aggregated Data with Group Functions

6.1 Aggregate Functions & NULL Mechanics 6.2 GROUP BY Clause & Aggregation Rules 6.3 Filtering Groups with the HAVING Clause

7 Chapter 7: Displaying Data from Multiple Tables (Joins)

7.1 ANSI/ISO SQL Equijoins & Natural Joins 7.2 Non-Equijoins, Self Joins, and Cartesian Products 7.3 Outer Joins: LEFT, RIGHT, and FULL

8 Chapter 8: Solving Queries with Subqueries

8.1 Single-Row Subqueries 8.2 Multiple-Row Subqueries & Inline Views 8.3 Correlated Subqueries & EXISTS Logic 8.4 Subquery Cardinality & NULL Traps

9 Chapter 9: Set Operators

9.1 UNION and UNION ALL Operators 9.2 INTERSECT and MINUS Operators 9.3 Set Operator Guidelines & ORDER BY Rules

10 Chapter 10: Managing Tables with DML & Transaction Control

10.1 INSERT, UPDATE, and DELETE Statements 10.2 MERGE and Multitable Inserts 10.3 Transaction Management: COMMIT, ROLLBACK, and SAVEPOINT

11 Chapter 11: Managing Tables and Relationships with DDL

11.1 CREATE TABLE & Oracle Built-In Datatypes 11.2 Integrity Constraints 11.3 Modifying Tables & Managing Constraints 11.4 Global Temporary Tables & External Tables

12 Chapter 12: Views

12.1 Simple vs Complex Views 12.2 DML Operations on Views & View Constraints

13 Chapter 13: Indexes, Synonyms, and Sequences

13.1 Sequences 13.2 Indexes 13.3 Synonyms

14 Chapter 14: Controlling User Access & Privileges

14.1 System Privileges 14.2 Object Privileges 14.3 Roles & Privilege Management

15 Chapter 15: Oracle Data Dictionary Views

15.1 Data Dictionary Structure & View Prefixes 15.2 Querying Schema & Object Metadata 15.3 Inspecting Constraints, Privileges, and Comments

16 Chapter 16: Managing Data in Different Time Zones

16.1 Datetime Data Types & Time Zone Offsets 16.2 Interval Data Types & Arithmetic 16.3 Time Zone Functions & Session Environment

Key Takeaways Explain This Topic AI

A sequence is an independent database object in the data dictionary that generates sequential, unique numeric integers primarily used for primary key surrogate values.

The NEXTVAL pseudo-column increments the sequence and returns the new value, while CURRVAL returns the current session value; referencing CURRVAL before NEXTVAL in a session raises ORA-08002.

NEXTVAL and CURRVAL are valid in top-level SELECT lists, INSERT VALUES clauses, and UPDATE SET clauses, but are strictly prohibited in WHERE, GROUP BY, HAVING, DISTINCT, and subqueries (raising ORA-02287).

Sequences guarantee uniqueness but do not guarantee gap-free numbers due to transaction rollbacks, instance crashes flushing SGA CACHE, and shared usage across tables.

ALTER SEQUENCE can modify INCREMENT BY, MAXVALUE, MINVALUE, CYCLE, and CACHE, but CANNOT modify START WITH (raising ORA-02283); the sequence must be dropped and recreated to change the starting value.

Last updated: August 2026

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

Parameter Clause Default Value Valid Range / Rules Technical Purpose & Exam Impact

INCREMENT BY n 1 Any non-zero integer (+ or -) Specifies the interval between sequence numbers. Positive values generate an ascending sequence; negative values generate a descending sequence. Cannot be 0 .

START WITH n 1 (ascending) / -1 (descending) Any integer within MINVALUE..MAXVALUE Specifies the first sequence number to be generated. Cannot be altered once the sequence is created.

MAXVALUE n NOMAXVALUE n >= START WITH and n > MINVALUE Defines the upper bound limit for the sequence.

NOMAXVALUE Default Ascending: 10^27 ( 999999999999999999999999999 ); Descending: -1 Instructs Oracle to use the maximum possible system limit for ascending sequences or -1 for descending sequences.

MINVALUE n NOMINVALUE n <= START WITH and n < MAXVALUE Defines the lower bound limit for the sequence.

NOMINVALUE Default Ascending: 1 ; Descending: -10^26 Instructs Oracle to use 1 as the minimum value for ascending sequences or -10^26 for descending sequences.

CYCLE NOCYCLE Requires MAXVALUE & MINVALUE defined Allows the sequence to continue generating numbers after reaching its limit. An ascending sequence wraps around to MINVALUE ; a descending sequence wraps around to MAXVALUE . (Note: Does NOT wrap to START WITH unless START WITH == MINVALUE ).

NOCYCLE Default N/A Prevents regeneration after reaching the limit. Attempting NEXTVAL beyond the limit raises ORA-08004: sequence exceeds MAXVALUE and cannot be instantiated .

CACHE n CACHE 20 n >= 2 (must be < CYCLE cache limit) Pre-allocates n integers in the SGA shared pool for high-speed retrieval. If CYCLE is enabled, CACHE must be strictly less than the number of values in the cycle.

NOCACHE Not default N/A Disables memory caching. Every NEXTVAL reference forces a synchronous dictionary write to update LAST_NUMBER , reducing throughput.

ORDER NOORDER N/A Guarantees that sequence numbers are generated exactly in the chronological order of request. Primarily used in Oracle Real Application Clusters (RAC).

NOORDER Default N/A Does not guarantee request order across RAC cluster nodes, optimizing parallel node throughput.

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

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.

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.

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.

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

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

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.

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.