Backend interview prep

Start

Oracle Sequences Interview Questions

Master Oracle Sequences with interview-focused questions covering NEXTVAL, CURRVAL, CACHE, NOCACHE, ORDER, NOORDER, CYCLE, Identity Columns, RAC behavior, performance tuning, and enterprise production best practices.

Introduction

In enterprise applications, every record usually requires a unique identifier.

Examples include

  • Customer ID
  • Employee ID
  • Order ID
  • Transaction ID
  • Invoice Number
  • Payment ID

Oracle provides Sequences to generate unique numeric values efficiently.

Sequences are

  • Highly Scalable
  • Thread Safe
  • Independent of Tables
  • Optimized for High Concurrency

They are one of the most frequently asked Oracle interview topics.

Oracle Sequence Architecture

flowchart LR

Application --> Sequence

Sequence --> NEXTVAL

NEXTVAL --> UniqueID

UniqueID --> InsertRow

1. What is an Oracle Sequence?

Answer

A Sequence is a database object that automatically generates unique numeric values.

It is commonly used for

  • Primary Keys
  • Order Numbers
  • Invoice IDs
  • Transaction Numbers

Unlike tables, sequences are independent database objects.

2. Why are Sequences required?

Without sequences

Application

↓

Find MAX(ID)

↓

+1

↓

Insert

Problems

  • Race Conditions
  • Duplicate IDs
  • Poor Performance

With sequences

NEXTVAL

↓

Unique Value

↓

Insert

3. How do you create a Sequence?

Example

CREATE SEQUENCE employee_seq
START WITH 1
INCREMENT BY 1;

4. What is NEXTVAL?

NEXTVAL generates

the next sequence number.

Example

SELECT employee_seq.NEXTVAL
FROM dual;

Output

1

2

3

4

Each call returns a new value.

NEXTVAL Flow

flowchart LR

Sequence --> NEXTVAL --> UniqueNumber --> Application

5. What is CURRVAL?

CURRVAL returns

the current sequence value

for the current session.

Example

SELECT employee_seq.CURRVAL
FROM dual;

CURRVAL can only be used after NEXTVAL has been called in the same session.

NEXTVAL vs CURRVAL

NEXTVAL CURRVAL
Generates Next Number Returns Current Number
Advances Sequence Does Not Advance
Always New Value Same Session Only

6. Can CURRVAL be used before NEXTVAL?

No.

Doing so raises

ORA-08002

sequence CURRVAL is not yet defined in this session

7. What is START WITH?

Defines the first value.

Example

CREATE SEQUENCE order_seq
START WITH 1000;

First generated value

1000

8. What is INCREMENT BY?

Defines how much the sequence increases.

Example

INCREMENT BY 5

Values generated

1

6

11

16

9. Can sequences decrement?

Yes.

Example

INCREMENT BY -1

Values

100

99

98

97

10. What is MINVALUE?

Specifies

minimum allowed value.

Example

MINVALUE 1

11. What is MAXVALUE?

Defines

maximum sequence value.

Example

MAXVALUE 999999

12. What is CACHE?

Oracle stores sequence numbers in memory.

Example

CACHE 100

Benefits

  • Fewer Disk Reads
  • Better Performance
  • Faster Inserts

CACHE Flow

flowchart LR

Sequence --> MemoryCache --> NEXTVAL --> Application

13. What is NOCACHE?

Disables caching.

Example

NOCACHE

Each sequence request accesses disk metadata.

Performance becomes slower.

CACHE vs NOCACHE

CACHE NOCACHE
Faster Slower
Uses Memory No Memory Cache
Best for Production Rarely Used

14. What is CYCLE?

Allows sequence numbers

to restart

after reaching MAXVALUE.

Example

1

2

3

↓

1

2

3

15. What is NOCYCLE?

Sequence stops after reaching MAXVALUE.

Default option.

Recommended for primary keys.

16. What is ORDER?

ORDER guarantees

sequence values

are generated

in request order.

Mostly useful in RAC environments.

17. What is NOORDER?

NOORDER allows Oracle

to generate values

without preserving request order.

Provides better performance.

Default option.

ORDER vs NOORDER

ORDER NOORDER
Maintains Order Better Performance
RAC Coordination Less Coordination
Slightly Slower Faster

18. Can sequence values have gaps?

Yes.

Gaps occur because of

  • Rollbacks
  • Database Restart
  • Cached Values Lost
  • Session Failure

Example

100

101

103

102 may never be used.

19. Can sequences generate duplicate values?

Normally no.

Duplicates occur only if

  • Sequence is altered incorrectly
  • CYCLE is used with reused values
  • Manual inserts conflict

20. Does rollback reset sequence values?

No.

Example

NEXTVAL

↓

100

↓

Rollback

↓

Next NEXTVAL

↓

101

Sequence numbers are never rolled back.

21. Can multiple tables use one sequence?

Yes.

One sequence

can generate IDs

for multiple tables.

22. Can multiple sequences exist?

Yes.

Example

Customer Sequence

Order Sequence

Invoice Sequence

Employee Sequence

23. What are Identity Columns?

Oracle 12c introduced

Identity Columns.

Example

CREATE TABLE employee (

id NUMBER GENERATED ALWAYS AS IDENTITY,

name VARCHAR2(100)

);

Oracle automatically creates an internal sequence.

Sequence vs Identity Column

Sequence Identity Column
Separate Object Built Into Table
More Flexible Easier to Use
Reusable Table Specific

24. What happens in Oracle RAC?

Each RAC node

may cache sequence values independently.

With

NOORDER

performance is better.

With

ORDER

Oracle coordinates value generation across nodes.

RAC Sequence Flow

flowchart LR

Node1 --> SequenceCache1

Node2 --> SequenceCache2

SequenceCache1 --> Insert

SequenceCache2 --> Insert

25. Banking Example

Transaction IDs

Generated using

transaction_seq

Ensures every transaction receives a unique number.

26. E-Commerce Example

Order IDs

order_seq

↓

100001

100002

100003

27. Payroll Example

Employee IDs

Generated automatically

using

employee_seq

28. Common Sequence Problems

  • CACHE too small
  • Using NOCACHE unnecessarily
  • Expecting gap-free numbers
  • Sharing one sequence incorrectly
  • Using CYCLE for primary keys
  • Calling CURRVAL before NEXTVAL

29. Performance Tips

  • Use CACHE in production.
  • Avoid NOCACHE unless required.
  • Prefer NOORDER unless strict ordering is necessary.
  • Use large cache sizes for high-volume inserts.
  • Do not rely on sequence values being consecutive.

30. Enterprise Best Practices

  • Use sequences for primary keys.
  • Never use MAX(id)+1.
  • Enable CACHE for production workloads.
  • Use NOCYCLE for unique identifiers.
  • Prefer NOORDER in non-RAC environments.
  • Monitor high-volume sequences.
  • Use identity columns for simple applications.
  • Separate sequences for unrelated business entities.
  • Avoid resetting production sequences.
  • Document sequence ownership clearly.

Sequence Workflow

flowchart LR

Application --> NEXTVAL --> UniqueID --> Insert --> Commit

Quick Revision

Topic Key Point
Sequence Generates Unique Numbers
NEXTVAL Next Value
CURRVAL Current Value
START WITH Initial Value
INCREMENT BY Step Size
CACHE Better Performance
NOCACHE No Memory Cache
CYCLE Restart Sequence
NOCYCLE Stop at Maximum
ORDER Preserve Order
NOORDER Faster Performance
Identity Column Auto-generated IDs

Interview Tips

Interviewers frequently ask

  • What is an Oracle Sequence?
  • NEXTVAL vs CURRVAL.
  • CACHE vs NOCACHE.
  • ORDER vs NOORDER.
  • Can sequences generate duplicate values?
  • Why do sequence numbers have gaps?
  • Does rollback reset sequence values?
  • Sequence vs Identity Column.
  • What happens to sequences in RAC?
  • Why shouldn't MAX(id)+1 be used?

A strong interview explanation is:

"Oracle Sequences generate unique numeric values independently of tables. NEXTVAL always returns a new value, while CURRVAL returns the last value generated in the current session. Sequence values are never rolled back, so gaps are normal. In production, CACHE and NOORDER are typically preferred for better performance, while NOCYCLE is recommended for primary key generation."

Summary

Oracle Sequences provide a scalable and thread-safe mechanism for generating unique numeric values. Features such as NEXTVAL, CURRVAL, CACHE, ORDER, CYCLE, and Identity Columns enable high-performance identifier generation across enterprise applications. Understanding sequence behavior, RAC considerations, caching strategies, and production best practices is essential for Oracle Developers, Database Engineers, DBAs, and Solution Architects building reliable Oracle-based systems.