Sequence Default Values

In Oracle, a sequence is a database object that generates a series of unique numbers, commonly used to create unique identifiers (e.g., primary keys) for rows in a table. Sequences have various parameters that control their behavior, including the default values that Oracle uses when a sequence is created without explicitly specifying certain options.

Here’s a detailed breakdown of the default values for sequences in Oracle:

1. START WITH Default Value

• Default: 1
• When a sequence is created, if the START WITH clause is not specified, Oracle defaults the starting value of the sequence to 1. This means the sequence will start generating values from 1 unless specified otherwise.

Example:

CREATE SEQUENCE my_seq;

In this case, my_seq will start with a value of 1.

If you want the sequence to start with a value other than 1, you need to specify it in the START WITH clause:

CREATE SEQUENCE my_seq START WITH 100;

 

2. INCREMENT BY Default Value

• Default: 1
• The INCREMENT BY clause defines the value by which the sequence is incremented each time NEXTVAL is called. By default, if the INCREMENT BY clause is not specified, Oracle will increment the sequence by 1 each time it generates a new value.

Example:

CREATE SEQUENCE my_seq;

In this case, the sequence will increment by 1 by default.

If you want a different increment value, you can specify it:

CREATE SEQUENCE my_seq INCREMENT BY 10;

 

3. MINVALUE Default Value

• Default: 1
• The MINVALUE clause defines the minimum value that the sequence can generate. If it is not specified, Oracle will use the default minimum value of 1 for ascending sequences.

Example:

CREATE SEQUENCE my_seq;

In this case, my_seq will have a default minimum value of 1.

If you want a different minimum value, you can specify it:

CREATE SEQUENCE my_seq MINVALUE 100;

 

4. MAXVALUE Default Value

• Default: 10^27 (a very large number)
• The MAXVALUE clause defines the maximum value that the sequence can generate. If the MAXVALUE clause is not specified, Oracle defaults to 10^27 for ascending sequences, which is a very large number.

Example:

CREATE SEQUENCE my_seq;

In this case, the sequence will have a default maximum value of 10^27.

If you want a specific maximum value, you can specify it:

CREATE SEQUENCE my_seq MAXVALUE 1000;

 

5. CYCLE Default Value

• Default: NOCYCLE
• The CYCLE clause specifies whether the sequence should restart from the MINVALUE once it reaches the MAXVALUE. By default, if the CYCLE clause is not specified, Oracle will use NOCYCLE, meaning the sequence will stop once it reaches its maximum value.

Example:

CREATE SEQUENCE my_seq;

In this case, the sequence will not cycle, and once it reaches the maximum value, it will stop generating numbers.

If you want the sequence to cycle, you can specify it:

CREATE SEQUENCE my_seq CYCLE;

 

6. CACHE Default Value

• Default: 20
• The CACHE clause specifies how many sequence numbers Oracle should pre-allocate and keep in memory for faster access. If the CACHE clause is not specified, Oracle will default to a cache size of 20.

Example:

CREATE SEQUENCE my_seq;

In this case, the sequence will cache 20 numbers by default for better performance.

If you want to specify a different cache size, you can do so:

CREATE SEQUENCE my_seq CACHE 100;

 

7. NOCACHE Default Value

• Default: NOCACHE
• If the CACHE clause is not specified, Oracle defaults to NOCACHE. This means that no numbers are pre-allocated for the sequence, and each number is generated when it is requested. This can cause a performance overhead due to the need for disk access each time a new number is generated.

 

8. ORDER Default Value

• Default: NOORDER
• The ORDER clause controls whether sequence numbers are generated in order when using a Real Application Clusters (RAC) system. By default, ORDER is not used unless explicitly specified. When set to ORDER, Oracle guarantees that sequence numbers are generated in the order they are requested in RAC environments, though this may impact performance.

 

9. NOORDER Default Value

• Default: NOORDER
• If you do not specify the ORDER option, Oracle assumes that sequence numbers are not required to be generated in a strict order. This is the default behavior.

 

10. ALWAYS and EAGER Default Values (For Identity Columns)

• Default: ALWAYS (Identity Column Behavior)
• If you are using Oracle’s identity columns (which also use sequences in the background), the default behavior is ALWAYS. This means the sequence automatically generates a value for every insert statement, and you cannot provide a value for that column manually.

 

Sample Default Sequence Creation

Here’s an example of creating a sequence using only default values:

CREATE SEQUENCE my_seq;

In this case:

• The sequence will start at 1.
• The sequence will increment by 1.
• The minimum value will be 1.
• The maximum value will be 10^27.
• The sequence will not cycle.
• The sequence will cache 20 numbers.

If you want to customize the sequence, you can specify the parameters as needed.

 

Conclusion

By default, Oracle sequences have certain values assigned for key attributes like START WITH, INCREMENT BY, MINVALUE, MAXVALUE, and CACHE. While these default settings are sufficient for many use cases, you can modify them based on your specific requirements for sequence behavior, performance, and data consistency.

Let me know if you need further clarification or examples!