Oracle Database Object _ sequence

Oracle Database Object _ sequence

A sequence is a database object that is used to automatically generate a unique set of ordinal numbers.

A sequence is a shared object in which multiple users can work with ordinal numbers in a sequence.
A sequence is typically applied to the primary key column of a table, so that when data is inserted into a table, the primary key column uses the ordinal number in the sequence to ensure that the value of the primary key column is not duplicated.
In this way, a more reliable primary key value can be obtained instead of the method that produces the primary key value in the application.
Note: The sequence numbers in a series can be recycled after the sequence specifies the maximum and the recyclable attributes.

Creation, modification, and deletion of sequences

By default, users can create sequences in their own mode. If you want to create a sequence in another user's mode,
You must have create any sequence this system permission.

The command that creates the sequence is create SEQUENCE, whose full syntax format is:

CREATE SEQUENCE sequence Name

INCREMENT by N

START with N

MINVALUE N | Nominvalue

MAXVALUE N | Nomaxvalue

CYCLE | Nocycle

CACHE | NOCACHE

ORDER | Noorder

In the syntax format of this command, the remaining options are optional except for the sequence name.

N in each option is an integer.

Where the start with option specifies the number from which the ordinal of the sequence starts, by default it starts with its minimum value.
The INCREMENT by option specifies the magnitude of the ordinal increment in the sequence, that is, the next ordinal is much larger than the previous ordinal.
The sequence number can be incremented or decremented, so the number n in the increment by option can be a positive integer or a negative integer.
The MaxValue is used to specify the maximum value of the ordinal in the sequence.

If there is no maximum value, the Nomaxvalue option can be used instead of this option.
Similarly, the minvalue is used to specify the minimum value of the ordinal in the sequence, and the minimum value in the sequence must be less than or equal to its start value.
If the maximum value is specified for a sequence, the user will not be able to get the ordinal number from the sequence when the sequence number is consumed.
The option cycle allows the sequence number to be recycled.

When the user is using the maximum value in the sequence, the next ordinal that can be used is its minimum value.
Each time a user uses a sequence, the sequence is queried once.

If you put the sequence number in memory to buffer, then the user obtains the sequence number speed greatly.
The purpose of the option cache is to buffer the next n sequential numbers in the sequence in memory.
If you do not want to buffer, you can replace it with the NoCache option.

After the sequence has been created, it can be modified during use.

such as changing its maximum, minimum, increment, etc., but cannot modify the start value.
It is important to note that if a partial ordinal is already in use, the modification of the sequence affects only the subsequent sequence number, which does not work on the previously used ordinal number.

The command to modify the sequence is alter SEQUENCE.

Users can modify their own sequences, and if they want to modify the sequence of other users, they need to have alter any sequence this system permission.
The use of the ALTER sequence command is basically the same as the use of the Create Sequence command, as long as you replace the keyword create with an alter.

The command to delete a sequence is drop SEQUENCE.

The user can delete the sequence that he created, and if you want to delete another user's sequence, you want to have the drop any SEQUENCE system permission.
When the sequence is deleted, its information is removed from the data dictionary.

Use of sequences

For the user, the available resources in the sequence are the ordinal numbers that are included in the series.

You can use the Select command to get the available ordinal numbers, or you can apply the ordinal to DML statements and expressions.
If you want to use a sequence of other users, you must have SELECT permission on the sequence.

The sequence provides two pseudo-columns, nextval and currval, that are used to access the ordinal numbers in the sequence.
Where Nextval represents the next available sequence number, and Curryal represents the current ordinal number.
A sequence can be thought of as a pointer containing a series of ordinal numbers.
When a sequence has just been created, the pointer is positioned before the first ordinal, and each time an ordinal is obtained, the pointer moves back one position, so that the current ordinal in the sequence can be accessed with currval and the next sequence number is accessed with Nextval.

The first time you use the ordinal in a sequence, you must first access the Nextval pseudo-column so that the pointer points to the first ordinal.

The SELECT statement allows you to get an available ordinal number from the sequence.

For example:

SELECT Seq1.nextval from dual;

It is necessary to use table dual in the SELECT statement, because the SELECT statement will return several ordinal numbers based on the number of rows in the table, and each time the Nextval pseudo-column is accessed, the pointer moves backwards by one ordinal.

The Currval pseudo-column represents the current ordinal in the sequence, and the pointer does not move backwards while accessing the pseudo-column.

The Currval pseudo-column has the same reference method as the Nextval pseudo-column, and the reference format is: sequence name. Currval.
A sequence can also be applied to other forms of a SELECT statement.

For example:

SELECT Seq1.nextval, empno from Scott.emp;

In more cases, the role of a sequence provides a unique ordinal number for the primary key column or other column in the table.

For example:

INSERT into Scott.dept (Deptno, Dname) VALUES (Seq1.nextval, ' Lili ');

A sequence is a shared database object that allows users to directly use the sequence they create, and other users can access the current user's sequence as long as they have SELECT permission on the sequence.
If an ordinal is obtained by a user, then no other user can get the serial number.
That is, the sequence can be shared, but the sequence number is not shared.
The access operation for an ordinal in a sequence is implemented as a separate transaction, regardless of whether the execution of the transaction is successful or not.
If a transaction that contains a DML statement is rolled back, the operation on the sequence cannot be rolled back.

Note: If you are in a loop, the sequence number can be recycled by other users.

When you access ordinal numbers in a sequence, there may be a case of discontinuous sequence, either because the transaction has been rolled back or when multiple users have shared access to the same sequence.
When a user wants to access another user's sequence, not only does it have SELECT permission on the sequence, but it also qualifies the name of the sequence as a user before accessing it.
For example:

SELECT Sys.seq1.nextval from dual;

If you want to grant the SELECT permission of a sequence to another user, the appropriate Grant command format is:

GRANT SELECT on sequence name to user name;

Query for sequence information

A sequence is a database object, and its related information is stored in a data dictionary.

There are three data dictionaries associated with a sequence: user_sequences, all sequences, and dba_sequences.
The columns and meanings of the data dictionary user sequences are as follows:

Sequence_name sequence Name

Min_value Minimum value

Max_value Maximum Value

increment_by Increase

Cycle_flag whether the loop

Order_flag whether the order

Cache_size Cache Size

Last_number Next available value

Note: Order_flag's explanation:

Note that the ORDER option was only necessary to guarantee ordered generation if you were using Oracle with the Parallel Ser ver option in parallel mode.
If you is using exclusive mode, sequence numbers is always generated in order.
Last_number's explanation:

The last sequence number used or cached is generally greater than the last value in the buffer.

The Last_number column is not updated during normal database operations and is used in database restart/restore operations.

Cache_size's explanation:

After the ordinal in-memory cache is exhausted, add the ordinal to the memory.

For example:

SELECT Order_flag, last_number,max_value,cache_size from user_sequences where sequence_name= ' SEQ1 ';

Oracle Database Object _ sequence