Sequence的作用
sequence是一种可以生成唯一数字的方案对象.如果说的再严谨些,是一轮循环中生成唯一的值.
Sequence 创建
SQL> create sequence dao_seq1 ;
Sequence created
当然这种使用了大量的默认属性.
关于这些属性在后面Sequence属性部分会有一个详细的说明.
Sequence 使用与12C新特性
使用currval
currval 伪列可以获取序列当前的值.但是不能在创建序列后立即使用.
SQL> select dao_seq1.currval from dual ;
select dao_seq1.currval from dual
ORA-08002: sequence DAO_SEQ1.CURRVAL is not yet defined in this session
这时候需要使用nextval先生成sequence的值.
使用nextval
SQL> select dao_seq1.nextval from dual ;
NEXTVAL
----------
1
SQL> select dao_seq1.nextval from dual ;
NEXTVAL
----------
2
SQL> select dao_seq1.currval from dual ;
CURRVAL
----------
2
SQL> select dao_seq1.nextval from dual ;
NEXTVAL
----------
3
SQL> select dao_seq1.currval from dual ;
CURRVAL
----------
3
向表中插入序列的值
SQL> create table dao_t1 (c1 number,c2 number) ;
Table created
SQL> insert into dao_t1 values (dao_seq3.nextval,1) ;
1 row inserted
SQL> select * from dao_t1 ;
C1 C2
---------- ----------
5 1
使用sequence作为列的默认值
create table dao_t2 (c1 number default dao_seq1.nextval primary key,c2 number) ;
Table created
SQL> create table dao_t2 (c1 number default dao_seq1.nextval primary key,c2 number) ;
Table created
SQL> insert into dao_t2 (c2) values(1) ;
1 row inserted
SQL> select * from dao_t2 ;
C1 C2
---------- ----------
21 1
使用标识列
上面两个例子都需要明确的引用一个列,并且序列是共享的对象,也可以被其他程序使用,也就是无法保证序列的连续性.
Oracle 12C 推出了标识列.
其实这个特性和MySQL中的auto_increment 相同.只是Oracle更强大.
SQL> create table dao_t3 (c1 number generated by default as identity ( start with 2 increment by 3 ) primary key ,c2 number) ;
Table created
SQL> insert into dao_t3 (c2) values (1) ;
1 row inserted
SQL> insert into dao_t3 (c2) values (2) ;
1 row inserted
SQL> select * from dao_t3 l
2 ;
C1 C2
---------- ----------
2 1
5 2
Sequence 属性
start with 指定序列的第一个值为多少.如果不指定默认值为1
SQL> create sequence dao_seq2 start with 5 ;
Sequence created
SQL> select dao_seq2.nextval from dual ;
NEXTVAL
----------
5
increment by 指定每次增长的步长.
SQL> create sequence dao_seq3 start with 5 increment by 2 ;
Sequence created
maxvalue & nomaxvalue
指定(不指定)sequence的最大值.如果指定最大值,不允许循环,则达到序列值会报错.
SQL> create sequence dao_seq4 maxvalue 5 nocycle ;
Sequence created
SQL> select dao_seq4.nextval from dual ;
NEXTVAL
----------
1
SQL> select dao_seq4.nextval from dual ;
NEXTVAL
----------
2
SQL> select dao_seq4.nextval from dual ;
NEXTVAL
----------
3
SQL> select dao_seq4.nextval from dual ;
NEXTVAL
----------
4
SQL> select dao_seq4.nextval from dual ;
NEXTVAL
----------
5
SQL> select dao_seq4.nextval from dual ;
select dao_seq4.nextval from dual
ORA-08004: sequence DAO_SEQ4.NEXTVAL exceeds MAXVALUE and cannot be instantiated
minvalue &&nominvalue
序列的最小值.通常与递减序列(increment by 为负数)搭配使用. 指定序列最小的值是多少
SQL> create sequence dao_seq5 start with 5 increment by -1 minvalue 3 maxvalue 5;
Sequence created
SQL> select dao_seq5.nextval from dual ;
NEXTVAL
----------
5
SQL> select dao_seq5.nextval from dual ;
NEXTVAL
----------
4
SQL> select dao_seq5.nextval from dual ;
NEXTVAL
----------
3
SQL> select dao_seq5.nextval from dual ;
select dao_seq5.nextval from dual
ORA-08004: sequence DAO_SEQ5.NEXTVAL goes below MINVALUE and cannot be instantiated
##cycle & nocycle ;
允许(禁止)循环.如果允许循环必须要指定cache.
SQL> create sequence dao_seq6 maxvalue 5 cycle cache 2;
Sequence created
SQL> create sequence dao_seq6 maxvalue 5 cycle cache 2;
Sequence created
SQL> select dao_seq6.nextval from dual ;
NEXTVAL
----------
1
SQL> select dao_seq6.nextval from dual ;
NEXTVAL
----------
2
SQL> select dao_seq6.nextval from dual ;
NEXTVAL
----------
3
SQL> select dao_seq6.nextval from dual ;
NEXTVAL
----------
4
SQL> select dao_seq6.nextval from dual ;
NEXTVAL
----------
5
SQL> select dao_seq6.nextval from dual ; --在此处开始循环
NEXTVAL
----------
1
SQL> select dao_seq6.nextval from dual ;
NEXTVAL
----------
2
特别注意:开始循环后序列将从minvalue 指定的值开始,而不是创建序列时的strat with指定的数字.
如果不指定minvalue则从1开始
SQL> create sequence dao_seq7 start with 3 maxvalue 5 increment by 1 cycle cache 2 ;
Sequence created
SQL> select dao_seq7.nextval from dual ;
NEXTVAL
----------
3
SQL> select dao_seq7.nextval from dual ;
NEXTVAL
----------
4
SQL> select dao_seq7.nextval from dual ;
NEXTVAL
----------
5
SQL> select dao_seq7.nextval from dual ; --达到最大值开始循环,由于没有指定minvalue 所以从1开始
NEXTVAL
----------
1
SQL> select dao_seq7.nextval from dual ;
NEXTVAL
----------
2
再看一下指定minvalue的例子
SQL> create sequence dao_seq8 start with 3 minvalue 2 maxvalue 5 increment by 1 cycle cache 2 ;
Sequence created
SQL> select dao_seq8.nextval from dual ;
NEXTVAL
----------
3
SQL> select dao_seq8.nextval from dual ;
NEXTVAL
----------
4
SQL> select dao_seq8.nextval from dual ;
NEXTVAL
----------
5
SQL> select dao_seq8.nextval from dual ; --指定了minvalue 2 从minvalue开始
NEXTVAL
----------
2
SQL> select dao_seq8.nextval from dual ;
NEXTVAL
----------
3
order &&no order
order 保证序列按顺序提供值
noorder 不保证序列按顺序提供值.
cache && nocache
cache 指定将多少个序列值缓存到cache中.此举可极大提高效率. nocache 则是不缓存序列值到内存中.
Sequence 查看与常用脚本
下面是一个重建sequence的脚本.写的比较基础,主要是给大家参考
select 'create sequence'||t.sequence_owner||'.'||t.sequence_name ||
' start with ' ||t.last_number ||' increment by '||t.increment_by
|| ' minvalue '||t.min_value ||' maxvalue '||t.max_value
|| (case when cache_size =0 then ' nocache ' else ' cache '||t.cache_size end )
|| (case when t.cycle_flag ='Y' then ' cycle ' else ' nocycle '||t.cache_size end )
|| (case when t.order_flag ='Y' then ' order ' else ' noorder '||t.cache_size end )
from dba_sequences t
where sequence_owner='DAO' ;
Sequence 与等待事件
使用序列经常会引起
SQL> create sequence dao_seq9 nocache ;
Sequence created
在两个或以上会话中执行下面的脚本
begin
loop
insert into dao_t1(c1) values (dao_seq9.nextval) ;
end loop ;
end ;
查看下等待事件
SQL> select T.SID,T.SERIAL#,T.EVENT,T.P1TEXT,T.P1
2 from v$session t
3 where t.TYPE ='USER'
4 AND T.SCHEMANAME='DAO'
5 AND T.SID != (SELECT SID FROM V$MYSTAT WHERE ROWNUM=1) ;
SID SERIAL# EVENT P1TEXT P1
---------- ---------- ---------------------------------------------------------------- ---------------------------------------------------------------- ----------
25 43019 row cache lock cache id 13
37 43523 library cache: mutex X idn 527062158
39 34433 SQL*Net message from client driver id 1413697536
56 45897 SQL*Net message from client driver id
SQL> select t.TYPE,t.CACHE#,t.PARAMETER from v$rowcache t where cache#=13 ;
TYPE CACHE# PARAMETER
----------- ---------- --------------------------------
PARENT 13 dc_sequences
等待事件发生的原因为没有设置cache属性,两个session再引用序列的值后都需要修改数据字典.
而在同一时刻,修改操作只能由一个session进行.
解决方法为修改序列增加cache 值.
SQL> alter sequence dao_seq9 cache 1000 ;
Sequence altered
再次在两个session中 运行刚才的程序
begin
loop
insert into dao_t1(c1) values (dao_seq9.nextval) ;
end loop ;
end ;
然后查询等待事件
SQL> select T.SID,T.SERIAL#,T.EVENT,T.P1TEXT,T.P1
2 from v$session t
3 where t.TYPE ='USER'
4 AND T.SCHEMANAME='DAO'
5 AND T.SID != (SELECT SID FROM V$MYSTAT WHERE ROWNUM=1) ;
SID SERIAL# EVENT P1TEXT P1
---------- ---------- ---------------------------------------------------------------- ---------------------------------------------------------------- ----------
25 43019 library cache: mutex X idn 527062158
37 43523 library cache: mutex X idn 527062158
39 34433 SQL*Net message from client driver id 1413697536
56 45897 SQL*Net message from client driver id 1413697536
但是需要注意使用cache
属性可能会造成序列输出的值不连续.
SQL> create sequence dao_seq10 cache 5 ; --创建序列指定cache
Sequence created.
SQL> SQL> select dao_seq10.nextval from dual ; --当前值为1 下一个值应该为2
NEXTVAL
----------
1
SQL> startup force
ORACLE instance started.
Total System Global Area 754974720 bytes
Fixed Size 2928968 bytes
Variable Size 545263288 bytes
Database Buffers 201326592 bytes
Redo Buffers 5455872 bytes
Database mounted.
Database opened.
SQL> select dao_seq10.nextval from dual ;
NEXTVAL
----------
6
原因为 刚创建时start with 为 1 cache 5 则Oracle 会将[1,2,3,4,5] 都放入内存中.然后修改数据字典中dao_seq10 的last_number为 6 重启后,内存中的值丢失,从数据字典读last_number 并将[6,7,8,9,10]放入到内存中.同时返回最小的值6.