Oracle sequence 使用总结

Oracle sequence 使用总结

碰到一点 sequence 使用不理解的地方，正好又有时间，查询了一些资料，整理了这篇文章，不恰当之处还望留言斧正

一、create sequence 语法参数解析

0、create sequence 语法格式

CREATE SEQUENCE [ schema. ] sequence_name
[ SHARING = { METADATA | DATA | NONE } ]
[ { INCREMENT BY | START WITH } integer
| { MAXVALUE integer | NOMAXVALUE }
| { MINVALUE integer | NOMINVALUE }
| { CYCLE | NOCYCLE }
| { CACHE integer | NOCACHE }
| { ORDER | NOORDER }
| { KEEP | NOKEEP }
| { SCALE {EXTEND | NOEXTEND} | NOSCALE }
| { SESSION | GLOBAL }
]...
;


• CREATE SEQUENCE

1、sequence_name

Specify the name of the sequence to be created. The name must satisfy the requirements listed in "Database Object Naming Rules".
If you specify none of the clauses INCREMENT BY through GLOBAL, then you create an ascending sequence that starts with 1 and increases by 1 with no upper limit. Specifying only INCREMENT BY -1 creates a descending sequence that starts with ‐1 and decreases with no lower limit.
● To create a sequence that increments without bound, for ascending sequences, omit the MAXVALUE parameter or specify NOMAXVALUE. For descending sequences, omit the MINVALUE parameter or specify the NOMINVALUE.
● To create a sequence that stops at a predefined limit, for an ascending sequence, specify a value for the MAXVALUE parameter. For a descending sequence, specify a value for the MINVALUE parameter. Also specify NOCYCLE. Any attempt to generate a sequence number once the sequence has reached its limit results in an error.
● To create a sequence that restarts after reaching a predefined limit, specify values for both the MAXVALUE and MINVALUE parameters. Also specify CYCLE.


Database Object Naming Rules

指定序列名字。

• 如果不加任何参数，则创建一个起始为1，步长为1，并且没有最大值限制的序列（NOMAXVALUE 实际有最大值限制，1028-1，即：28个9）；

• 如果仅指定 INCREMENT BY -1，则创建一个起始为-1，步长为-1，且没有最小值限制的序列（NOMINVALUE 实际有最小值限制，-(1027 -1)，即：-27个9）；

2、SHARING 参数

This clause applies only when creating a sequence in an application root. This type of sequence is called an application common object and it can be shared with the application PDBs that belong to the application root. To determine how the sequence is shared, specify one of the following sharing attributes:
● METADATA - A metadata link shares the sequence’s metadata, but its data is unique to each container. This type of sequence is referred to as a metadata-linked application common object.
● DATA - A data link shares the sequence, and its data is the same for all containers in the application container. Its data is stored only in the application root. This type of sequence is referred to as a data-linked application common object.
● NONE - The sequence is not shared.
If you omit this clause, then the database uses the value of the DEFAULT_SHARING initialization parameter to determine the sharing attribute of the sequence. If the DEFAULT_SHARING initialization parameter does not have a value, then the default is METADATA.
You cannot change the sharing attribute of a sequence after it is created.

See Also:
Oracle Database Reference for more information on the DEFAULT_SHARING initialization parameter
Oracle Database Administrator’s Guide for complete information on creating application common objects


Oracle Database Reference

Oracle Database Administrator’s Guide

应用程序容器序列共享参数，常规序列使用不到该参数，否则报错：ORA-65021: illegal use of SHARING clause

• METADATA 仅共享序列元数据，数据在每个容器内不同；
• DATA 共享序列元数据及数据，数据在所有容器内相同
• NONE 序列不共享

序列创建后，其 sharing 属性不能修改。

3、INCREMENT BY 参数

Specify the interval between sequence numbers. This integer value can be any positive or negative integer, but it cannot be 0. This value can have 28 or fewer digits for an ascending sequence and 27 or fewer digits for a descending sequence. The absolute of this value must be less than the difference of MAXVALUE and MINVALUE. If this value is negative, then the sequence descends. If the value is positive, then the sequence ascends. If you omit this clause, then the interval defaults to 1.


指定序列间隔（步长）。

• 可以正整数、负整数，不可以为0，默认为1；
• 指定值得绝对值要小于 maxvalue 和 minvalue 的差；
• 正整数为升序，负整数为降序；

4、START WITH 参数

Specify the first sequence number to be generated. Use this clause to start an ascending sequence at a value greater than its minimum or to start a descending sequence at a value less than its maximum. For ascending sequences, the default value is the minimum value of the sequence. For descending sequences, the default value is the maximum value of the sequence. This integer value can have 28 or fewer digits for positive values and 27 or fewer digits for negative values.

Note:This value is not necessarily the value to which an ascending or descending cycling sequence cycles after reaching its maximum or minimum value, respectively.


指定序列起始值，即第一个序列号

升序序列默认为序列的最小值；

降序序列默认为序列的最大值；

5、MAXVALUE 参数

Specify the maximum value the sequence can generate. This integer value can have 28 or fewer digits for positive values and 27 or fewer digits for negative values. MAXVALUE must be equal to or greater than START WITH and must be greater than MINVALUE.


指定序列的最大值，指定一个数值限制序列的最大值。

必须 ≥ START WITH 值，并且 ＞ MINVALUE 值

6、NOMAXVALUE 参数

​ Specify NOMAXVALUE to indicate a maximum value of 1028-1 for an ascending sequence or -1 for a descending sequence. This is the default.

指定不限制序列最大值（默认参数）

参数指定后，默认升序序列的最大值1028-1，即：28位9，降序序列的最大值-1

7、MINVALUE 参数

Specify the minimum value of the sequence. This integer value can have 28 or fewer digits for positive values and 27 or fewer digits for negative values. MINVALUE must be less than or equal to START WITH and must be less than MAXVALUE.


指定序列最小值，指定一个数值限制序列的最小值。

必须 ≤ START WITH 值，并且 ＜ MAXVALUE 值

8、NOMINVALUE 参数

​ Specify NOMINVALUE to indicate a minimum value of 1 for an ascending sequence or -(1027 -1) for a descending sequence. This is the default.

指定不限制序列最小值（默认参数）

参数指定后，默认升序序列最小值为 1，降序序列最小值为 -(1027 -1)

9、CYCLE 参数

Specify CYCLE to indicate that the sequence continues to generate values after reaching either its maximum or minimum value. After an ascending sequence reaches its maximum value, it generates its minimum value. After a descending sequence reaches its minimum, it generates its maximum value.


指定序列循环生成

参数指定后，当升序序列达到其最大值后，下一个将生成其最小值；当降序序列达到其最小值后，下一个将生成其最大值；

10、NOCYCLE 参数

Specify NOCYCLE to indicate that the sequence cannot generate more values after reaching its maximum or minimum value. This is the default.


指定序列不循环生成（默认参数）

序列达到最大值或最小值后，将无法继续生成，报错 ORA-08004

11、CACHE 参数

Specify how many values of the sequence the database preallocates and keeps in memory for faster access. This integer value can have 28 or fewer digits. The minimum value for this parameter is 2. For sequences that cycle, this value must be less than the number of values in the cycle. You cannot cache more values than will fit in a given cycle of sequence numbers. Therefore, the maximum value allowed for CACHE must be less than the value determined by the following formula:
CEIL ( (MAXVALUE - MINVALUE) / ABS (INCREMENT) )
If a system failure occurs, then all cached sequence values that have not been used in committed DML statements are lost. The potential number of lost values is equal to the value of the CACHE parameter.

Note:Oracle recommends using the CACHE setting to enhance performance if you are using sequences in an Oracle Real Application Clusters environment.


指定预分配缓存到内存中序列值数量

值 ≥ 2，cache 和 nocahe 参数都不指定，则默认为 cache 20，为提高效率，应该设置的少大一些，比如1000或者2000；

指定后可能导致某些序列值无法使用，预缓存到内存中，此时序列已分配，如果系统故障，内存中序列未使用完，则未使用部分不再分配

12、NOCACHE 参数

Specify NOCACHE to indicate that values of the sequence are not preallocated. If you omit both CACHE and NOCACHE, then the database caches 20 sequence numbers by default.


指定不预分配序列值

cache 和 nocahe 参数都不指定，则默认为 cache 20

13、ORDER 参数

Specify ORDER to guarantee that sequence numbers are generated in order of request. This clause is useful if you are using the sequence numbers as timestamps. Guaranteeing order is usually not important for sequences used to generate primary keys.


指定保证按顺序生成序列号

使用序列号当做一个时间戳时有用处；而如果是对于用以生成主键时并不重要；

14、NOORDER 参数

Specify NOORDER if you do not want to guarantee sequence numbers are generated in order of request. This is the default.


指定不保证按顺序生成序列号（默认设置）

指定后，数据库只是尽可能的按顺序生成序列号

15、KEEP 参数

Specify KEEP if you want NEXTVAL to retain its original value during replay for Application Continuity. This behavior will occur only if the user running the application is the owner of the schema containing the sequence. This clause is useful for providing bind variable consistency at replay after recoverable errors. Refer to Oracle Database Development Guide for more information on Application Continuity.


Oracle Database Development Guide

指定是否要在重演期间保留其原始值以实现应用程序连续性。

对可恢复性错误重演是提供绑定变量一致性有用

16、NOKEEP 参数

Specify NOKEEP if you do not want NEXTVAL to retain its original value during replay for Application Continuity. This is the default.

Note:The KEEP and NOKEEP clauses apply only to the owner of the schema containing the sequence. You can control whether NEXTVAL retains its original value for other users during replay for Application Continuity by granting or revoking the KEEP SEQUENCE object privilege on the sequence. Refer to Table 18-2 for more information on the KEEP SEQUENCE object privilege.


keep 的反作用，默认设置

17、SCALE 参数（12C特性）

Use SCALE to enable sequence scalability. When SCALE is specified, a numeric offset is affixed to the beginning of the sequence which removes all duplicates in generated values.


指定创建可伸缩序列

用于优化使用序列作为主键列，当insert过于频繁时，出现索引块分裂的问题

• EXTEND 类型

If you specify EXTEND with SCALE the generated sequence values are all of length (x+y), where x is the length of the scalable offset (default value is 6), and y is the maximum number of digits in the sequence (maxvalue/minvalue).
When you use SCALE it is highly recommended that you not use ORDER simultaneously on the sequence.


表示序列总长度=[X个数字+Y个数字]，X默认值是6位数，Y是MAXVALUE指定的位数。

• NOEXTEND 类型（scale 的默认属性）

NOEXTEND is the default setting for the SCALE clause. With the NOEXTEND setting, the generated sequence values are at most as wide as the maximum number of digits in the sequence (maxvalue/minvalue). This setting is useful for integration with existing applications where sequences are used to populate fixed width columns.


表示序列总长度不能超过MAXVALUE定义的长度，由于前面默认是6位数+正常的序列号，所以长度最少是7位数。

• 对于EXTEND来说MAXVALUE代表的后面正常序列的长度，而不是可伸缩序列的总长度。
• 对于NOEXTED来说MAXVALUE代表的是可伸缩序列的总长度。

注：Oracle强烈建议对于可伸缩序列不要使用order属性，因为它本身就是无序的。

18、NOSCALE 参数

Use NOSCALE to disable sequence scalability.


指定序列不可伸缩

19、SHARD 参数

For complete semantics on the SHARD clause please refer to the SHARD clause of the ALTER SEQUENCE statement.


参考 ALTER SEQUENCE

20、SESSION 参数

Specify SESSION to create a session sequence, which is a special type of sequence that is specifically designed to be used with global temporary tables that have session visibility. Unlike the existing regular sequences (referred to as "global" sequences for the sake of comparison), a session sequence returns a unique range of sequence numbers only within a session, but not across sessions. Another difference is that session sequences are not persistent. If a session goes away, so does the state of the session sequences that were accessed during the session.
Session sequences must be created by a read-write database but can be accessed on any read-write or read-only databases (either a regular database temporarily open read-only or a standby database).
The CACHE, NOCACHE, ORDER, or NOORDER clauses are ignored when specified with the SESSION clause.

See Also:
Oracle Data Guard Concepts and Administration for more information on session sequences


Oracle Data Guard Concepts and Administration

指定创建会话序列，仅自己会话中可用，会话消失则会话序列也会消失。

会话序列将忽略 cache、nocache、order、noorder 参数

21、GLOBAL 参数

Specify GLOBAL to create a global, or regular, sequence. This is the default.


指定创建全局序列（常规序列，默认设置）

22、使用样例

CREATE SEQUENCE testseq MINVALUE 1 MAXVALUE 9999 INCREMENT BY 1 START WITH 1 NOCACHE NOORDER CYCLE NOKEEP NOSCALE GLOBAL;
CREATE SEQUENCE testseq INCREMENT BY -1 MINVALUE 1 MAXVALUE 99 START WITH -1 CACHE 100 NOORDER;


二、dba_sequences(all_sequences)视图

官方描述

ALL_SEQUENCES describes all sequences accessible to the current user.

Related Views

• DBA_SEQUENCES describes all sequences in the database.
• USER_SEQUENCES describes all sequences owned by the current user. This view does not display the SEQUENCE_OWNER column.

所有列中比较有歧义的就是 last_number，表述的不清楚，也不是字面理解意思。

• 使用 cache 的话，表示序列缓存的最后一个值；
• 不使用cache时，表示预计的下一个序列值，即：当前序列值+increment by 步长，此时可能会大于maxvalue
• last_number 值一般要比当前序列值大；

使用序列命令

• sequence.CURRVAL – 返回 sequence的当前值
• sequence.NEXTVAL – 增加sequence的值，然后返回 sequence 值

使用样例

select testseq.nextval from dual;
select testseq.currval from dual; -- 序列未使用过，会返回错误 ORA-08002
select last_number from all_sequences where sequence_name='TESTSEQ';