The Invoice Numbers With 132 Gaps

NEXTVAL is allocated outside transaction control: once a value is drawn it is consumed forever, regardless of what happens to the transaction that drew it. Three events in that month each produce gaps:

1. The failed nightly load (ORA-00001). The trigger fired, NEXTVAL was consumed, then the insert failed on the unique constraint. Rollback does not return the number. Every failed row in that run burned a value - this is usually the largest contributor.
2. Two instance bounces. CACHE 20 keeps 20 values in memory per instance. On shutdown or crash, unused cached values are discarded and never reissued. That is up to 20 lost per bounce, and again on each restart if the first session aborts.
3. The rolled-back test. INSERT + ROLLBACK still consumed NEXTVAL. Rollback undoes rows, never sequence state.

Also counted: any invoice the trigger numbered that was later deleted leaves a hole in the table.

NOCACHE only removes cause 2. It shrinks the cache-loss window to zero (at a small performance cost from serialising NEXTVAL), but causes 1 and 3 remain, so the auditor's requirement would still be violated. A sequence is designed to be concurrent and non-transactional; gap-freedom is not a property it can offer at any cache setting. Only a mechanism that is part of the same transaction as the insert can avoid gaps from failed or rolled-back work.

START WITH sets the initial value used when the sequence is created (or restarted). On an existing sequence it edits the stored metadata but does not reset NEXTVAL - the sequence continues from its current high-water mark, which is why the next invoice came out around 4,313. The correct statement in Oracle Database 19c is:

ALTER SEQUENCE seq_inv RESTART START WITH 5000;

RESTART repositions the sequence; it can optionally take a new START WITH. (The alternative, DROP + CREATE, invalidates every object that referenced the sequence and requires re-granting privileges.) Before restarting, be sure no existing INVOICE_NO is at or above the new start, or the next insert fails with ORA-00001.

Cost of gap-free numbering. You must serialise the allocation with the insert, so the number is only consumed if the transaction commits:

UPDATE invoice_ctr SET last_no = last_no + 1 RETURNING last_no INTO :n;
INSERT INTO invoices (invoice_no, ...) VALUES (:n, ...);
COMMIT;

Consequences: the single counter row is locked until commit, so concurrent invoicing degenerates to one-at-a-time throughput; the counter table is a hot block and a contention point; and the promise still breaks if a transaction commits but the invoice is later deleted, or if the application prints the number before commit and the commit then fails. Gap-free numbering is an accounting control that trades concurrency for a guarantee - decide that consciously rather than by setting NOCACHE.

Both variants use a sequence behind the scenes; the difference is who is allowed to supply the value.

• GENERATED ALWAYS AS IDENTITY rejects any user-supplied value for the column with ORA-32795: insert into generated always identity column not allowed. The only override is the explicit OVERRIDING SYSTEM VALUE clause, which the legacy loader does not use. That is exactly the failure described: the loader supplies INVOICE_NO values copied from the old system, and the column refuses them.
• GENERATED BY DEFAULT AS IDENTITY accepts a supplied value and uses the sequence only when the column is omitted. The trap is silent: supplying a value does not advance the internal sequence, so the next generated number can collide with a migrated one and raise ORA-00001 - the same class of failure, but discovered later and harder to trace.

The two honest options for a migration:

1. Keep GENERATED ALWAYS and change the loader to omit INVOICE_NO, remapping external references - no key collisions, but historical invoice numbers change.
2. Keep the historical numbers, use BY DEFAULT, and after the data load synchronise the sequence to the high-water mark (ALTER SEQUENCE ... RESTART START WITH <max+1>). This preserves the numbers but means the identity is only a default, not a guarantee.

A third option - leave the trigger and the sequence in place rather than converting to identity - is often the least disruptive, provided the gap discussion above has been had with the auditor.