Oracle Serial Number Details

Example 1: Create sequence:
Create sequence abc increment by 1 start with 1 MAXVALUE 9999999999 nocycle nocache;

Syntax explanation
Create sequence name
[Increment by n] -- 1,
[Start with n] -- 2,
[{MAXVALUE/MINVALUE n | NOMAXVALUE}] -- 3,
[{CYCLE | NOCYCLE}] -- 4,
[{CACHE n | NOCACHE}]; -- 5,

Where:
1. increment by is used to define the step size (growth volume) of a sequence. If omitted, the default value is 1. If a negative value is displayed, the sequence value decreases according to the step size.

2. start with defines the initial value of the sequence (that is, the first value generated). The default value is 1.

3. MAXVALUE defines the maximum value that a sequence generator can generate.
The "NOMAXVALUE" option is the default option, indicating that no maximum value is defined. In this case, the maximum value generated by the system for an incremental sequence is the 27 power of 10. For a descending sequence, the maximum value is-1.
MINVALUE defines the minimum value that can be generated by the sequence generator.
The "NOMAXVALUE" option is the default option, indicating that there is no minimum value defined. What is the minimum value that the system can produce for the descending sequence? 10 to the power of 26; for incremental sequence, the minimum value is 1.

4. CYCLE and NOCYCLE indicate whether to CYCLE when the value of the sequence generator reaches the limit value. CYCLE indicates loop, and NOCYCLE indicates no loop.
If there is a loop, when the ascending sequence reaches the maximum value, it loops to the minimum value. When the descending sequence reaches the minimum value, it loops to the maximum value.
If there is no loop, an error occurs when a new value is generated after the limit value is reached.

5. CACHE (buffer) defines the size of the memory block for storing the sequence. The default value is 20. NOCACHE indicates no memory buffer for the sequence.
Buffer the sequence memory to improve the sequence performance.

Example 2: delete sequence:
Drop sequence name;

Example 3: Obtain the current and next values of the sequence:

SELECT sequence name. currval from dual; -- get the current value of the sequence
SELECT sequence name. nextval from dual; -- get the next value of the sequence

Example 4: view the sequence
The data dictionary USER_OBJECTS allows you to view User sequences.
You can view the sequence settings through the data dictionary USER_SEQUENCES.

SELECT * FROM USER_OBJECTS;
SELECT * FROM USER_SEQUENCES;