**What is a Sequence?**

- A sequence is a user created database object that can be shared by multiple users to generate unique integers.
- A typical usage for sequences is to create a primary key value, which must be unique for each row.
- The sequence is generated and incremented (or decremented) by an internal Oracle routine.
- This can be a time-saving object because it can reduce the amount of application code needed to write a sequence-generating routine.
- Sequence numbers are stored and generated independently of tables. Therefore, the same sequence can be used for multiple tables.

**CREATE SEQUENCE**statement, which has the following syntax:

- The default start_num is 1.
- The default increment number is 1.
- The absolute value of increment_num must be less than the difference between maximum_num and minimum_num.
- minimum_num must be less than or equal to start_num, and minimum_num must be less than maximum_num.
- NOMINVALUE specifies the maximum is 1 for an ascending sequence or -10^26 for a descending sequence.
- NOMINVALUE is the default.
- maximum_num must be greater than or equal to start_num, and maximum_num must be greater than minimum_num.
- NOMAXVALUE specifies the maximum is 10^27 for an ascending sequence or C1 for a descending sequence.
- NOMAXVALUE is the default.
- CYCLE specifies the sequence generates integers even after reaching its maximum or minimum value.
- When an ascending sequence reaches its maximum value, the next value generated is the minimum.
- When a descending sequence reaches its minimum value, the next value generated is the maximum.
- NOCYCLE specifies the sequence cannot generate any more integers after reaching its maximum or minimum value.
- NOCYCLE is the default.
- CACHE cache_num specifies the number of integers to keep in memory.
- The default number of integers to cache is 20.
- The minimum number of integers that may be cached is 2.
- The maximum integers that may be cached is determined by the formula CEIL(maximum_num – minimum_num)/ABS(increment_num).
- NOCACHE specifies no integers are to be stored.
- ORDER guarantees the integers are generated in the order of the request.
- You typically use ORDER when using Real Application Clusters.
- NOORDER doesn’t guarantee the integers are generated in the order of the request.
- NOORDER is the default

**Creating a sequence and then get the next value**

**Once initialized, you can get the current value from the sequence using currval.**

**You can’t use CURRVAL just after a sequence creation. It will throw an error.**

**When you select currval , nextval remains unchanged; nextval only changes when you select nextval to get the next value.**

**Getting Information on Sequences**

You get information on your sequences from

**user_sequences.**

**Modifying a Sequence**

- You modify a sequence using the
**ALTER SEQUENCE**statement. - You cannot change the start value of a sequence.
- The minimum value cannot be more than the current value of the sequence ( currval ).
- The maximum value cannot be less than the current value of the sequence ( currval ).

**Removing a Sequence**

• Remove a sequence from the data dictionary by using the

**DROP SEQUENCE**statement.

• Once removed, the sequence can no longer be referenced.