Oracle SQL Certification 1Z0-071 Course | Section 14: Managing Sequences

In this section, you’ll learn how sequences work, how to create them, and how to use them effectively when inserting data into tables.

Sequences are a powerful Oracle database object used to generate unique numeric values automatically. They are most commonly used to create primary keys, but they can also be used anywhere an automatically increasing number is required.

Unlike tables, sequences do not store data rows. Instead, they act as number generators that produce values when requested.

In this section, you’ll learn how sequences work, how to create them, and how to use them effectively when inserting data into tables.

Lesson 1: Sequences – Overview and Creation

A sequence is a database object that automatically generates unique numbers. It behaves like a counter that increases each time a value is requested.

Sequences are typically used to generate primary key values when inserting new records.

Why Use Sequences

Sequences help you:

  • Generate unique identifiers automatically
  • Avoid duplicate primary key values
  • Simplify insert operations
  • Support high-concurrency environments

Unlike manually assigning numbers, sequences guarantee uniqueness even when many users insert rows simultaneously.

Creating a Sequence

Sequences are created using the CREATE SEQUENCE statement.

Example:

CREATE SEQUENCE employee_seq
START WITH 1
INCREMENT BY 1;

This creates a sequence that begins at 1 and increases by 1 each time a new value is generated.

Important Sequence Options

When creating a sequence, you can control how it behaves using several options.

START WITH

Defines the first value generated.

START WITH 100

INCREMENT BY

Controls how much the sequence increases each time.

INCREMENT BY 5

MAXVALUE / MINVALUE

Define the highest and lowest values the sequence can generate.

MAXVALUE 10000
MINVALUE 1

CYCLE vs NOCYCLE

  • CYCLE → sequence restarts after reaching the maximum value
  • NOCYCLE → sequence stops when the maximum value is reached

If you omit options, Oracle applies default behavior.

Removing a Sequence

If a sequence is no longer needed, it can be removed using:

DROP SEQUENCE employee_seq;

Be careful — once dropped, the sequence and its settings are permanently removed.

Lesson 2: Using Sequences

Once a sequence is created, you must request values from it. Oracle provides two special pseudocolumns for this.

NEXTVAL

NEXTVAL generates the next number in the sequence.

Example:

SELECT employee_seq.NEXTVAL
FROM dual;

Each time this is executed, the sequence increases.

Example output:

1
2
3
4
CURRVAL

CURRVAL returns the current value of the sequence within the session.

Example:

SELECT employee_seq.CURRVAL
FROM dual;

However, there is an important rule:

CURRVAL cannot be used before NEXTVAL has been called in the session.

Oracle must generate the first value before it can reference the current one.

Using Sequences in INSERT Statements

Sequences are most commonly used when inserting new rows.

Example:

INSERT INTO employees (employee_id, first_name, salary)
VALUES (employee_seq.NEXTVAL, 'John', 5000);

Here, the sequence automatically generates the primary key value.

Why Sequences Skip Numbers

You may notice sequences sometimes skip values.

This can happen because:

  • Transactions are rolled back
  • Multiple sessions request values simultaneously
  • Oracle preallocates sequence numbers internally

This behavior is normal and expected.

Sequences guarantee uniqueness, not perfect numeric order.

CYCLE Behavior

If a sequence reaches its MAXVALUE and the CYCLE option is enabled, it restarts from the minimum value.

Example:

MAXVALUE 10
CYCLE

The sequence would produce:

1,2,3,4,5,6,7,8,9,10,1,2...

If NOCYCLE is used, Oracle raises an error when the maximum value is reached.

MINVALUE Behavior

If MINVALUE is not defined, Oracle assigns a default minimum value, depending on the sequence configuration.

This ensures the sequence always has a valid lower boundary.

Final Thoughts

Sequences are a fundamental Oracle feature used to generate unique numeric values automatically.

They are essential for:

  • Creating primary keys
  • Supporting concurrent inserts
  • Avoiding duplicate identifiers
  • Automating number generation

Understanding how sequences behave — especially NEXTVAL, CURRVAL, and sequence options — is important both for the Oracle 1Z0-071 exam and for real-world database development.

Make sure to practice creating sequences with different options and inserting rows using them to fully understand their behavior.