Traditional sequences
Oracle sequences have existed since early versions and are still widely used in PL/SQL code:
CREATE SEQUENCE seq_order_id
START WITH 1
INCREMENT BY 1
NOCYCLE
CACHE 100; -- cache 100 values for performance
Use in DML:
INSERT INTO orders (order_id, ...) VALUES (seq_order_id.NEXTVAL, ...);
-- Or read current value (same session):
SELECT seq_order_id.CURRVAL FROM DUAL;
Identity columns (Oracle 12c+)
Modern Oracle supports identity columns natively, similar to other databases:
CREATE TABLE orders (
order_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id NUMBER,
amount NUMBER
);
-- Insert without specifying order_id:
INSERT INTO orders (customer_id, amount) VALUES (101, 5000);
GENERATED ALWAYS prevents manual ID insertion (good for consistency). Use GENERATED BY DEFAULT if you need to occasionally specify values (e.g. data migrations).
Sequence caching and performance
The CACHE clause pre-allocates sequence numbers in memory. Without caching, every NEXTVAL call requires a redo log write. With CACHE 100, Oracle grabs 100 values at once — much faster for high-concurrency inserts.
Downside: on instance restart, uncached values are lost, creating gaps in the sequence. Gaps are normal and expected — never design logic that assumes a gapless sequence.
SYS_GUID for distributed systems
For systems where multiple databases independently generate IDs that must merge:
INSERT INTO events (event_id, ...) VALUES (SYS_GUID(), ...);
SYS_GUID() returns a 16-byte globally unique identifier (RAW type). More storage than NUMBER but eliminates ID collision in distributed architectures.
Getting the generated ID after INSERT
INSERT INTO orders (...) VALUES (seq_order_id.NEXTVAL, ...)
RETURNING order_id INTO v_new_order_id;
Or for identity columns:
INSERT INTO orders (customer_id, amount) VALUES (101, 5000)
RETURNING order_id INTO v_new_id;
Avoid SELECT MAX(order_id) after insert — not safe in concurrent environments.