Oracle Database / Operations & Incident Response
Explain Oracle sequence gaps without treating them as lost rows
Sequences can skip values after rollback, cache loss or concurrent allocation. A gap does not automatically imply a missing committed business record.
Environment and inputs: Oracle Database 19c baseline · SQL*Plus-compatible SQL unless RMAN, DGMGRL, ADRCI or a host shell is specified. Check release, patch level, edition and container first.
Start with this diagnostic
Use the stated SQL, RMAN, DGMGRL or host shell and replace sample names with approved targets. DBA and V$ views need authorized catalog access in the correct container. Do not grant broad privileges for an example.
SELECT sequence_owner, sequence_name, cache_size, increment_by, last_number FROM all_sequences WHERE sequence_owner = 'APP_OWNER' AND sequence_name = 'ORDER_SEQ';Read the result
With caching, LAST_NUMBER is not simply the last value returned to an application. Sequence allocation is separate from transaction commit.
Resolve and verify
Reconcile actual business records and the application's identifier rules. If gap-free numbering is required, design that requirement separately with concurrency and audit controls.
Operational precautions
Do not reset the sequence to fill gaps or assume NOCACHE guarantees gap-free business numbering.