Sequences and Synonyms

This chapter covers two small but essential Oracle schema objects: sequences (which generate unique numbers for primary keys) and synonyms (which give an object an alternative name).

They are independent features but are commonly introduced together because both belong to the "schema utility" family of objects you create alongside tables.

Part 1 — Sequences

A sequence is a database object that generates a stream of unique numbers. The most common use is to feed those numbers into a primary key column.

Why You Need Sequences

Imagine you're inserting employees into a table:

INSERT INTO employees (employee_id, first_name, last_name) VALUES (1, 'Alice', 'Adams');
INSERT INTO employees (employee_id, first_name, last_name) VALUES (2, 'Bob',   'Brown');
INSERT INTO employees (employee_id, first_name, last_name) VALUES (3, 'Carol', 'Clark');

What happens with 100 concurrent users inserting at once? You can't have each one query MAX(employee_id) + 1 — they'd all see the same value and collide. Sequences solve this by handing out numbers atomically, one at a time, with no two callers ever getting the same number.

Creating a Sequence

CREATE SEQUENCE emp_seq
  START WITH 1000
  INCREMENT BY 1
  MINVALUE 1000
  MAXVALUE 9999999
  NOCACHE
  NOCYCLE;

A minimal sequence is just:

CREATE SEQUENCE order_seq;

This creates a sequence starting at 1, incrementing by 1, no cycle, with a cache of 20.

Using NEXTVAL and CURRVAL

Two pseudocolumns interact with a sequence:

• sequence_name.NEXTVAL — advances the sequence and returns the new value
• sequence_name.CURRVAL — returns the most recent value retrieved by this session

-- Get the next value
SELECT emp_seq.NEXTVAL FROM dual;

Result: 1000

-- Inspect the current value (only valid AFTER you've called NEXTVAL in this session)
SELECT emp_seq.CURRVAL FROM dual;

Result: 1000

-- Each call to NEXTVAL increments:
SELECT emp_seq.NEXTVAL FROM dual;  -- 1001
SELECT emp_seq.NEXTVAL FROM dual;  -- 1002

Using a Sequence in INSERT

The typical pattern is to call NEXTVAL in the column list:

INSERT INTO employees (employee_id, first_name, last_name, email)
VALUES (emp_seq.NEXTVAL, 'Diane', 'Davis', 'diane@company.com');

You can also call NEXTVAL inside a SELECT-driven insert:

INSERT INTO new_employees (employee_id, first_name, last_name)
SELECT emp_seq.NEXTVAL, first_name, last_name
FROM   raw_employee_data;

Sequences with a BEFORE INSERT Trigger (Legacy Pattern)

Before Oracle 12c, the canonical pattern was a trigger that auto-fills the PK:

CREATE OR REPLACE TRIGGER emp_bi
BEFORE INSERT ON employees
FOR EACH ROW
WHEN (NEW.employee_id IS NULL)
BEGIN
  :NEW.employee_id := emp_seq.NEXTVAL;
END;
/

Now any INSERT that omits employee_id gets one assigned automatically:

INSERT INTO employees (first_name, last_name) VALUES ('Eve', 'Evans');
-- employee_id is automatically set to emp_seq.NEXTVAL

IDENTITY Columns (Oracle 12c+)

In Oracle 12c and later, you can skip the explicit sequence + trigger and use an IDENTITY column:

CREATE TABLE employees (
  employee_id NUMBER GENERATED ALWAYS AS IDENTITY
              (START WITH 1000 INCREMENT BY 1),
  first_name  VARCHAR2(50),
  last_name   VARCHAR2(50)
);

-- INSERT does not mention employee_id
INSERT INTO employees (first_name, last_name) VALUES ('Frank', 'Foster');

Three modes are available:

Under the hood, Oracle still creates a sequence to back the IDENTITY column — you can see it in USER_SEQUENCES with a system-generated name like ISEQ$$_12345.

Modifying and Dropping Sequences

-- Change increment, max, or cycle:
ALTER SEQUENCE emp_seq INCREMENT BY 10;
ALTER SEQUENCE emp_seq MAXVALUE 100000 CYCLE;

-- Reset?  Oracle does NOT support "reset to N" directly.
-- You drop and recreate, or use a trick with negative increment:
ALTER SEQUENCE emp_seq INCREMENT BY -999 MINVALUE 0;
SELECT emp_seq.NEXTVAL FROM dual;   -- consumes the negative step
ALTER SEQUENCE emp_seq INCREMENT BY 1;

-- Drop:
DROP SEQUENCE emp_seq;

CACHE vs NOCACHE — The Trade-off

CACHE n tells Oracle to pre-allocate n numbers in memory. Subsequent NEXTVAL calls are very fast (no disk access) until the cache is exhausted.

Pros of CACHE:

• Much faster under high insert load
• Default CACHE 20 is fine for most cases; large bulk loads use CACHE 1000 or more

Cons of CACHE:

• If the instance crashes, all cached but unused numbers are lost — you'll see gaps
• Not suitable if you need a strict, gapless sequence (e.g., invoice numbers for tax compliance)

-- Strict gapless sequence — slow but no gaps from cache loss
CREATE SEQUENCE invoice_seq NOCACHE ORDER;

Inspecting Sequences

SELECT sequence_name, min_value, max_value, increment_by, cache_size, last_number
FROM   user_sequences;

Result:

LAST_NUMBER is the next value to be issued after the cache (not the most recently issued one). Don't read it as "the current value".

Part 2 — Synonyms

A synonym is an alias — an alternative name — for another database object: a table, view, sequence, procedure, or another synonym.

Why Use Synonyms?

Without synonyms, accessing another user's table requires the fully qualified name:

SELECT * FROM hr.employees;

Synonyms let you write the simpler form:

SELECT * FROM employees;

This matters for:

1. Cross-schema access — apps connecting as app_user can read hr.employees as just employees
2. Backwards compatibility — rename a table and create a synonym to the new name so old code keeps working
3. Abstraction — point a synonym at a view or remote table without changing application code
4. Database links — synonyms hide that the object lives on a remote database

Private Synonyms

A private synonym is visible only to the user who created it. It is stored in the user's own schema.

-- I'm logged in as 'analyst'.  I create a synonym for hr.employees:
CREATE SYNONYM emp FOR hr.employees;

-- Now I can query simply:
SELECT * FROM emp;

analyst.emp exists. Other users can't see it.

Public Synonyms

A public synonym is visible to every user in the database. It's not stored in any user's schema — it lives in the special PUBLIC namespace.

-- Requires CREATE PUBLIC SYNONYM privilege (usually DBA only):
CREATE PUBLIC SYNONYM countries FOR hr.countries;

Every user can now write SELECT * FROM countries and get hr.countries, provided they have SELECT privilege on the underlying table.

Name Resolution Order

When Oracle sees an unqualified name like employees, it resolves in this order:

1. A column of the FROM tables
2. An object in the current user's schema
3. A private synonym the user owns
4. A public synonym visible to everyone
5. Otherwise → ORA-00942: table or view does not exist

If analyst has both a local table employees and a public synonym employees, the local table wins.

CREATE OR REPLACE SYNONYM

You can update a synonym to point at a different object without dropping it:

CREATE OR REPLACE SYNONYM emp FOR hr.employees_v2;

This is useful during a deployment where you're rolling out a new version of a table or view: keep the synonym name stable, switch the target.

Dropping a Synonym

DROP SYNONYM emp;
DROP PUBLIC SYNONYM countries;

Dropping a synonym does not affect the underlying object — only the alias is removed.

Synonyms for Sequences

A common pattern is a public synonym for an application-wide sequence:

-- DBA setup:
CREATE SEQUENCE app.global_id_seq START WITH 1;
GRANT SELECT ON app.global_id_seq TO PUBLIC;
CREATE PUBLIC SYNONYM global_id_seq FOR app.global_id_seq;

-- Any user:
INSERT INTO my_table (id, ...) VALUES (global_id_seq.NEXTVAL, ...);

Inspecting Synonyms

-- All synonyms I can see:
SELECT synonym_name, table_owner, table_name, db_link
FROM   all_synonyms
WHERE  synonym_name = 'EMP';

-- Only my private synonyms:
SELECT * FROM user_synonyms;

-- Only public synonyms:
SELECT * FROM dba_synonyms WHERE owner = 'PUBLIC';

TABLE_OWNER and TABLE_NAME describe the target — they don't have to be a table; that column name is just historical.

When Synonyms Can Bite You

1. Stale synonym — the target table was dropped or renamed. The synonym remains and points at nothing. Queries fail with ORA-00980: synonym translation is no longer valid.
2. Naming collisions — a local table named the same as a public synonym silently hides the synonym. New developers may not realise they're querying a different object.
3. Performance illusions — a synonym pointing at a view that points at another view that points at a remote database can be very slow without the user realising why.

-- Detect stale synonyms:
SELECT s.synonym_name
FROM   all_synonyms s
LEFT   JOIN all_objects o
       ON  o.owner       = s.table_owner
       AND o.object_name = s.table_name
WHERE  s.owner = USER
  AND  o.object_name IS NULL;

Sequences and Synonyms Together — Worked Example

A clean schema pattern that many production apps use:

-- 1. Centralised app schema owns the sequence:
CREATE SEQUENCE app.customer_seq START WITH 1000 CACHE 1000;

-- 2. Grant access:
GRANT SELECT ON app.customer_seq TO web_user;

-- 3. Public synonym so the app uses a simple name:
CREATE PUBLIC SYNONYM customer_seq FOR app.customer_seq;

-- 4. Table with IDENTITY-style default referencing the synonym:
CREATE TABLE web_user.customers (
  customer_id  NUMBER DEFAULT customer_seq.NEXTVAL PRIMARY KEY,
  name         VARCHAR2(100),
  created_at   TIMESTAMP DEFAULT SYSTIMESTAMP
);

-- 5. Insert without ever naming the sequence:
INSERT INTO customers (name) VALUES ('Acme Corp');

DEFAULT seq.NEXTVAL on a column (Oracle 12c+) is the simplest way to attach a sequence to a column without a trigger.

Common Errors

Interview Corner

CREATE TABLE invoice_counter (next_id NUMBER NOT NULL);
INSERT INTO invoice_counter VALUES (1);

-- Inside the transaction:
UPDATE invoice_counter SET next_id = next_id + 1
RETURNING next_id - 1 INTO :new_id;

SELECT * FROM all_objects WHERE object_name = 'EMPLOYEES';
SELECT * FROM all_tab_privs WHERE table_name = 'EMPLOYEES' AND grantee = USER;

Related Topics

• DDL Commands — CREATE TABLE with IDENTITY and DEFAULT seq.NEXTVAL
• Constraints — primary keys are the most common use of sequences
• DML Commands — INSERT with seq.NEXTVAL
• Views — synonyms are often used in front of views for stability
• Performance — CACHE size affects insert performance significantly