Oracle Database object_sequence

Oracle Database object_sequence

A sequence is a database object used to automatically generate a unique sequence number.

A sequence is a shared object. Multiple users can use the sequence number together.
Generally, the sequence is applied to the primary key columns of the table. When data is inserted into the table, the sequence number in the primary key column is used to ensure that the values of the primary key column are not repeated.
This method can be used to generate primary key values in applications and obtain more reliable primary key values.
Note: After the maximum value and the cyclic attribute are specified in the sequence, the sequence number can be used cyclically.

 

Sequence creation, modification, and deletion

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

The command for creating a SEQUENCE is create sequence, and its complete syntax format is:

Create 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, all other options except the sequence name are optional.

N in each option is an integer.

The start with option specifies the number starting from which the sequence number starts. By default, it starts from its minimum value.
The increment by option specifies the ascending range of sequence numbers, that is, the value of the next sequence number is greater than the previous sequence number.
The sequence number can increase or decrease. Therefore, the number n in the increment by option can be a positive integer or a negative integer.
MAXVALUE is used to specify the maximum value of the sequence number.

If there is no maximum value, use the NOMAXVALUE option instead.
Similarly, MINVALUE is used to specify the minimum value of the sequence number. The minimum value in the sequence must be smaller than or equal to its start value.
If the maximum value is specified for the sequence, the sequence number cannot be obtained from the sequence when it is consumed.
The CYCLE option allows sequence numbers to be used cyclically.

When the user is using the maximum value in the sequence, the next sequence number that can be used is its minimum value.
Each time you use a sequence, you must query the sequence.

If the sequence number is cached in the memory, the user can get the sequence number much faster.
The function of option CACHE is to buffer the next n sequence numbers in the memory.
If you do not want to buffer it, you can replace it with the NOCACHE option.

After a sequence is created, it can be modified during use.

For example, you can modify the maximum value, minimum value, and increment value, but cannot modify the start value.
Note that if some sequence numbers are used, modifications to the sequence will only affect subsequent sequence numbers, but will not affect the sequence numbers used previously.

The command to modify the SEQUENCE is alter sequence.

You can modify your SEQUENCE. If you want to modify the SEQUENCE of another user, you must have the system permission of alter any sequence.
The use of the alter sequence command is basically the same as that of the create sequence command. You only need to replace the keyword "CREATE" with "ALTER.

The command to delete a SEQUENCE is drop sequence.

You can delete sequences created by yourself. To delete sequences of other users, you must have the permission to drop any sequence system.
After a sequence is deleted, its related information is deleted from the data dictionary.

Sequence usage

For users, the available resources in the sequence are the serial numbers contained in them.

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

The sequence provides two pseudo columns, NEXTVAL and CURRVAL, which are used to access the serial numbers in the sequence.
NEXTVAL indicates the next available sequence number, and CURRYAL indicates the current sequence number.
A sequence can be considered as a pointer containing a series of serial numbers.
When a sequence is created, the pointer is located before the first sequence number. After a sequence number is obtained, the pointer moves backward to a position. In this case, you can use CURRVAL to access the current sequence number in the sequence, use NEXTVAL to access the next sequence number.

When using sequence numbers for the first time, you must first access the NEXTVAL pseudo column to point the pointer to the first sequence number.

 

You can use the SELECT statement to obtain an available sequence number from the sequence.

For example:

SELECT seq1.nextval FROM dual;

In the SELECT statement, it is necessary to use the table dual, because the SELECT statement returns several sequence numbers based on the number of rows in the table, and each time the NEXTVAL pseudo column is accessed, the pointer moves one Sequence Number Backward.

The CURRVAL pseudo column represents the current serial number in the sequence. when accessing this pseudo column, the pointer does not move backward.

The reference method of the CURRVAL pseudo column is the same as that of the NEXTVAL pseudo column. The reference format is: sequence name. currval.
Sequences can also be used in other forms of SELECT statements.

For example:

SELECT seq1.nextval, empno FROM scott. emp;

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

For example:

Insert into scott. dept (deptno, dname) VALUES (seq1.nextval, 'lili ');

A sequence is a shared database object. You can directly use the sequence created by yourself. Other users can also access the sequence of the current user as long as they have the SELECT permission on the sequence.
If a sequence number is obtained by a user, other users cannot obtain the sequence number.
That is to say, sequences can be shared, but the sequence numbers cannot be shared.
The access operation of serial numbers in the sequence is implemented as a separate transaction. The execution of this transaction is irrelevant to the execution of other transactions.
If a transaction that contains a DML statement is rolled back, operations on the sequence cannot be rolled back.

Note: If the sequence is in a cyclable sequence, the sequence number can be used repeatedly by other users.

Sequence numbers in the access sequence may be discontinuous because transactions are rolled back or multiple users access the same sequence.
When a user wants to access the sequence of other users, he must not only have the SELECT permission for the sequence, but also specify the user name before the sequence name.
For example:

SELECT sys. seq1.nextval FROM dual;

If you want to GRANT the SELECT permission of a sequence to other users, the corresponding GRANT command format is:

Grant select on sequence name TO user name;

Query of sequence information

As a database object, sequence information is stored in the data dictionary.

Sequence-related data dictionaries include USER_SEQUENCES, all sequences, and DBA_SEQUENCES.
The columns and meanings of user sequences in the data dictionary are as follows:

SEQUENCE_NAME sequence name

MIN_VALUE minimum value

MAX_VALUE maximum

INCREMENT_BY increase

CYCLE_FLAG Loop

Whether ORDER_FLAG is sequential

CACHE_SIZE cache size

LAST_NUMBER the next available value

Note: ORDER_FLAG explanation:

Note that the ORDER option is only necessary to guarantee ordered generation if you are using Oracle with the Parallel Server option in parallel mode.
If you are using exclusive mode, sequence numbers are always generated in order.
LAST_NUMBER explanation:

The last serial number used or cached, generally greater than the last value in the buffer.

The LAST_NUMBER column is not updated during normal database operations. It is used in database restart/recovery operations.

CACHE_SIZE explanation:

After the serial number cached in the memory is used up, add the serial number to the memory.

For example:

SELECT order_flag, last_number, max_value, cache_size from user_sequences where sequence_name = 'seq1 ';