序列使用cache值的缺陷

列cache的默认值是20，使用cache的好处是：可以加快获得序列值。
但坏处是如果非正常关库时，保存在内存中的所有用户的序列值，全部丢失，导致序列值跳跃。
甚至把cache值保持到sharepool中，异常关机照样会丢失序列值。
所以，shut abort慎用，或者用之前使用以下sql，把序列cache值不为0的改为0，等库重启后，再改为原值。
sys用户下执行：
SQL> set pages 9000
SQL> set lines 400
SQL> spool seq_cache.txt
SQL> select 'alter sequence '||sequence_owner||'.'||sequence_name||' cache '||CACHE_SIZE||';' from dba_sequences where cache_size!=0;
SQL> spool off
把结果保存到一个文件中。

把cache值不为0的序列改为0：
select 'alter sequence '||sequence_owner||'.'||sequence_name||' nocache;' from dba_sequences where cache_size!=0;
把结果执行。

 

测试过程：
SQL> show user
USER is "SCOTT"
SQL> select scott_seq.nextval from dual;

   NEXTVAL
----------
        65

SQL> show user
USER is "SYS"

SQL> select sys_seq.nextval from dual; 

   NEXTVAL
----------
        26

SQL> select JOBSEQ.nextval from dual;

   NEXTVAL
----------
        48

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  238530560 bytes
Fixed Size                  1335724 bytes
Variable Size             150998612 bytes
Database Buffers           83886080 bytes
Redo Buffers                2310144 bytes
Database mounted.
Database opened.
SQL> select JOBSEQ.nextval from dual;

   NEXTVAL
----------
        49    

SQL> select sys_seq.nextval from dual; 

   NEXTVAL
----------
        27   --正常关库没有导致序列丢失

[oracle@db ~]$ sqlplus scott/tiger

SQL*Plus: Release 11.2.0.1.0 Production on Sun Aug 19 11:40:22 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select scott_seq.nextval from dual;

   NEXTVAL
----------
        66  --正常关库没有导致序列丢失

SQL> show user
USER is "SYS"
SQL> shut abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  238530560 bytes
Fixed Size                  1335724 bytes
Variable Size             150998612 bytes
Database Buffers           83886080 bytes
Redo Buffers                2310144 bytes
Database mounted.
Database opened.
SQL> select sys_seq.nextval from dual; 

   NEXTVAL
----------
        47

SQL> select JOBSEQ.nextval from dual;

   NEXTVAL
----------
        69   --异常关库导致sys用户的序列递增了20

[oracle@db ~]$ sqlplus scott/tiger

SQL*Plus: Release 11.2.0.1.0 Production on Sun Aug 19 11:42:35 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select scott_seq.nextval from dual;

   NEXTVAL
----------
        86   --异常关库导致scott用户的序列递增了20

再把JOBSEQ保持到sharepool，看看异常关库会不会导致该序列数值跳跃。
SQL> exec dbms_shared_pool.keep('JOBSEQ','Q');

PL/SQL procedure successfully completed.

SQL> show user
USER is "SYS"
SQL> shut abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  238530560 bytes
Fixed Size                  1335724 bytes
Variable Size             150998612 bytes
Database Buffers           83886080 bytes
Redo Buffers                2310144 bytes
Database mounted.
Database opened.

SQL> select JOBSEQ.nextval from dual;

   NEXTVAL
----------
        89   --看来dbms_shared_pool.keep()不能防止序列跳跃

 

把三个序列设为nocache，再做异常关库
SQL> alter sequence sys_seq nocache;

Sequence altered.

SQL> alter sequence jobseq nocache;

Sequence altered.

SQL> alter sequence scott.scott_seq nocache;

Sequence altered.

SQL> shut abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  238530560 bytes
Fixed Size                  1335724 bytes
Variable Size             150998612 bytes
Database Buffers           83886080 bytes
Redo Buffers                2310144 bytes
Database mounted.
Database opened.
SQL> select sys_seq.nextval from dual; 

   NEXTVAL
----------
        48   --此时序列是正常的

SQL> select JOBSEQ.nextval from dual;

   NEXTVAL
----------
        70

SQL> select scott.scott_seq.nextval from dual; 

   NEXTVAL
----------
        87  --此时序列正常了。

看来shut abort 执行前，要先做的一步是把cache值不为0的序列，改为0，才能防止序列值跳跃。

 

 

以下是序列的语法和用法：
sequence 序列
 流水号(整数)递增或递减
 存在于library cache
 通常为主键或唯一约束提供值
 减少编写生成序列的程序代码量
 比如QQ号的产生
 

语法
 CREATE SEQUENCE seq_name
 [INCREMENT BY n]
 [START WITH n]   3    1
 [{MAXVALUE N | NOMAXVALUE}]
 [{MINVALUE N | NOMINVALUE}]
 [{CYCLE | NOCYCLE}]
 [{CACHE N|NOCACHE}];

 

INCREMENT BY  N表示步长
START WITH N N表示初始值
[NO]MAXVALUE N N表示有没有最大 最大值是多少 NOMINVALUE选项最大值时10^27
[NO]MINVALUE N  N最小值
CYCLE  达到最大是否循环回到初始值
[NO]CACHE 就是你每次在内存里预保存多少个值 免得每次都要获取 浪费资源 一次获取批量存在内存中

 

取值方法
 NEXTVAL 返回下一个值
 CURRVAL 返回当前值
钉(pin)缓存
start with 不能修改  历史回溯只能删了再建

SQL> create sequence my_seq;

Sequence created.

SQL> select my_seq.currval from dual;
select my_seq.currval from dual
       *
ERROR at line 1:
ORA-08002: sequence MY_SEQ.CURRVAL is not yet defined in this session

初始的序列必须用nextval先取值
SQL> select my_seq.nextval from dual;

   NEXTVAL
----------
  1

SQL> select my_seq.currval from dual;

   CURRVAL
----------
  1

SQL> 
通过nextval获取 序列就是在增长
SQL> select my_seq.nextval from dual;

   NEXTVAL
----------
  2

修改序列
 alter SEQUENCE seq_name
 [INCREMENT BY n]
 [START WITH n]
 [{MAXVALUE N | NOMAXVALUE}]
 [{MINVALUE N | NOMINVALUE}]
 [{CYCLE | NOCYCLE}]
 [{CACHE N|NOCACHE}];

SQL> alter sequence seq2 start with 10;
alter sequence seq2 start with 10
                    *
ERROR at line 1:
ORA-02283: cannot alter starting sequence number

SQL> alter sequence seq2 cache 10;

Sequence altered.

SQL> alter sequence seq2 cycle;

Sequence altered.