How to Create a Sequence in Oracle
Last updated August 25, 2026 · By the SaturnSQL team
Use CREATE SEQUENCE with NEXTVAL and CURRVAL, or an IDENTITY column (12c+) for the same result without a separate object. Sequences leave gaps by design.
CREATE SEQUENCE order_seq
START WITH 1
INCREMENT BY 1
CACHE 20;
INSERT INTO orders (order_id, customer_id, order_date)
VALUES (order_seq.NEXTVAL, 42, SYSDATE);
SELECT order_seq.CURRVAL FROM dual;IDENTITY columns as the modern alternative
Since Oracle 12c, GENERATED AS IDENTITY defines an implicit, per-column sequence without a standalone CREATE SEQUENCE statement, which is closer to what MySQL or Postgres users expect.
CREATE TABLE orders (
order_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id NUMBER,
order_date DATE DEFAULT SYSDATE
);Both approaches leave gaps in the numbers: CACHE pre-allocates a block of values to memory and any that are unused when the instance restarts are lost, and a rolled-back transaction never returns its NEXTVAL to the pool. This is expected behavior, not a bug; a sequence guarantees unique, increasing values, never a gap-free count. Never assume a sequence tracks row counts.
Run this in SaturnSQL
SaturnSQL is a browser-based SQL editor for teams: shared query library, schema-aware autocomplete, and scheduled exports to Google Sheets and Slack.
Try it free