Master Oracle SEQUENCE: Create, Use, Alter, Query & Drop with Real SQL Samples

1. What is an Oracle SEQUENCE?

A SEQUENCE generates a series of numeric values that can be used as primary keys or for any incremental numbering need. It resides in memory, does not consume disk space, and can be referenced in INSERT statements or queried for its current value.

2. Creating a SEQUENCE

The CREATE SEQUENCE statement requires the CREATE SEQUENCE system privilege. Its syntax includes optional clauses:

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

Key options:

INCREMENT BY : step size (default 1; negative values make the sequence decrement).

START WITH : first value generated (default 1).

MAXVALUE / MINVALUE : limits of the sequence; NOMAXVALUE/NOMINVALUE mean no explicit limit.

CYCLE / NOCYCLE : whether the sequence restarts after reaching its limit.

CACHE n : how many values are pre‑allocated in memory (default 20) to improve performance.

3. Using a SEQUENCE

Retrieve the next value with sequence_name.NEXTVAL and the current value with sequence_name.CURRVAL (CURRVAL is only valid after NEXTVAL has been called in the session).

-- Create a table
CREATE TABLE t1 (id NUMBER, qq NUMBER, ww NUMBER);

-- Insert using the sequence
INSERT INTO t1 VALUES (t1_seq.NEXTVAL, 1, 1);

-- Query the table
SELECT * FROM t1;

4. Modifying a SEQUENCE

Only the owner or a user with ALTER ANY SEQUENCE can change a sequence. ALTER affects only future values; the start value cannot be changed without dropping and recreating the sequence.

ALTER SEQUENCE t1_seq INCREMENT BY 2 MAXVALUE 10 CYCLE;

5. Querying SEQUENCE metadata

Use the data‑dictionary views to inspect sequences: USER_OBJECTS – lists objects owned by the user, including sequences. USER_SEQUENCES – shows definition details such as MIN_VALUE, MAX_VALUE, INCREMENT_BY, CACHE_SIZE, etc.

SELECT SEQUENCE_NAME, MIN_VALUE, MAX_VALUE, INCREMENT_BY, LAST_NUMBER
FROM USER_SEQUENCES;

6. Dropping a SEQUENCE

When a sequence is no longer needed, remove it with:

DROP SEQUENCE t1_seq;