相关链接
·listagg函数 ORA-01489: result
·Oracle 序列详解与12C新特性
·使用10053trace 揭秘 Oracle 12C
·Oracle分页写法大全
·Oracle进程和运行时限制

Oracle 序列详解与12C新特性

Sequence的作用

sequence是一种可以生成唯一数字的方案对象.如果说的再严谨些,是一轮循环中生成唯一的值.

Sequence 创建

QQ截图20170105165448.jpg

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.

 

上一篇:listagg函数 ORA-01489: result of string concatenation is too long的解决办法|下一篇:使用10053trace 揭秘 Oracle 12C TOPN 新特性

微信扫一扫
获取大数据学习资料