Which two statements are true about sequences in a single instance Oracle database?

Question

Options

• ASequences that start with 1 and increment by 1 can never have gaps.
• BA sequence can issue the same number more than once.
• CSequence numbers that are allocated require a COMMIT statement to make the allocation
• DA sequence can provide numeric values for more than one column or table.
• EThe data dictionary is always updated each time a sequence number is allocated.

• A
  3% (1)
• B
  94% (32)
• E
  3% (1)

Explanation

B and D are correct because a sequence is a standalone database object - completely independent of any table - meaning multiple tables and columns can call sequence_name.NEXTVAL freely (D). When a sequence is created with the CYCLE option, it wraps back to its starting value after reaching the maximum, reissuing previously generated numbers (B).

A is wrong because gaps are virtually unavoidable: a rolled-back transaction discards its allocated number, a database restart loses any cached-but-unused values, and concurrent sessions pulling numbers out of order all create gaps - even on a START WITH 1 INCREMENT BY 1 sequence.

C is wrong because sequence allocation is independent of transaction control - NEXTVAL increments the sequence immediately and permanently, which is precisely why a rollback doesn't reclaim the number (and thus why gaps happen).

E is wrong because Oracle uses a CACHE (default 20) to pre-allocate a block of numbers in memory; the data dictionary is only updated when the cache is exhausted or the instance restarts, not on every single NEXTVAL call - that's the whole point of caching for performance.

Memory tip: Think of a sequence as a shared ticket dispenser - any counter (table/column) can pull a ticket from it (D), tickets torn off but not used are gone forever creating gaps, the roll can loop around and reissue old numbers if CYCLE is set (B), and the refill log (data dictionary) only updates in batches, not per ticket.

No community discussion yet for this question.