[20150401]关于sequence问题.txt

[20150401]关于sequence问题.txt

--昨天别人关于sequence问题,要使用sequence保持连续实际上很困难,更多是关于跳号的问题,实际上这些都是无法避免的.

--自己也做一些测试:

1.建立测试环境:

SCOTT@test> @ver1

PORT_STRING                    VERSION        BANNER

------------------------------ -------------- --------------------------------------------------------------------------------

x86_64/Linux 2.4.xx            11.2.0.3.0     Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

SCOTT@test> create sequence seq2;

Sequence created.

SCOTT@test> select * from dba_sequences where sequence_name = 'SEQ2' and sequence_owner=user;

SEQUENCE_OWNER                 SEQUENCE_NAME                   MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER

------------------------------ ------------------------------ ---------- ---------- ------------ - - ---------- -----------

SCOTT                          SEQ2                                    1 1.0000E+28            1 N N         20           1

--缺省定义cache=20,增1.  CYCLE_FLAG=N, ORDER_FLAG =N.

SCOTT@test> select seq2.nextval from dual;

                      NEXTVAL

-----------------------------

                            1

                            2

SCOTT                          SEQ2                                    1 1.0000E+28            1 N N         20          21

--使用后LAST_NUMBER=21,也就是使用20完成后,下次从21开始.

2.测试异常关机的情况:

SYS@test> shutdown abort ;

ORACLE instance shut down.

   NEXTVAL

----------

        21

        22

--可以发现出现了跳号.

3.测试正常关机的情况:

SCOTT                          SEQ2                                    1 1.0000E+28            1 N N         20          23

--可以发现一个奇特的特性,正常关机是可以保证取号连续的.也就是讲在正常关机的情况下,系统会更新sys.seq$表.

        23

        24

4.刷新共享池呢?

SCOTT@test> alter system flush shared_pool;

System altered.

SCOTT                          SEQ2                                    1 1.0000E+28            1 N N         20          43

--可以推断刷新共享池以后,也会出现跳号情况.

        43

        44

5.如何知道现在系统那些顺序号在使用.

dba_sequences之类的视图是查询seq属性的,并无法确定那些正在使用的seq.

SCOTT@test> select distinct KGLHDNSP,KGLHDNSD from x$kglob;

  KGLHDNSP KGLHDNSD

---------- ----------------------------------------------------------------

         0 SQL AREA

         4 INDEX

         1 TABLE/PROCEDURE

         3 TRIGGER

        52 SCHEDULER EARLIEST START TIME

        64 EDITION

        69 DBLINK

         2 BODY

        79 ACCOUNT_STATUS

        10 QUEUE

        23 RULESET

        24 RESOURCE MANAGER

        73 SCHEMA

        74 DBINSTANCE

        51 SCHEDULER GLOBAL ATTRIBUTE

        75 SQL AREA STATS

        82 SQL AREA BUILD

         5 CLUSTER

        18 PUB SUB INTERNAL INFORMATION

19 rows selected.

select  * from x$kglob a where kglobtyd='SEQUENCE';

--很奇怪对应的KGLHDNSP,KGLHDNSD是1,TABLE/PROCEDURE.

SCOTT@test> column VIEW_DEFINITION format a100

SCOTT@test> select * from V$FIXED_VIEW_DEFINITION where view_name like '%_SEQUENCES%';

VIEW_NAME                      VIEW_DEFINITION

------------------------------ ----------------------------------------------------------------------------------------------------

GV$_SEQUENCES                  select inst_id, KGLNAOWN,KGLNAOBJ,KGLOBT08,decode(bitand(KGLOBT00,1),0,'N','Y'),decode(bitand(KGLOBT

                               00,2),0,'N','Y'),decode(bitand(KGLOBT00,16),0,'N','Y'),KGLOBTN0,KGLOBTN2,KGLOBTN3,KGLOBTN1,decode(bi

                               tand(KGLOBT09,1),0,'N','Y'),decode(bitand(KGLOBT09,2),0,'N','Y'),KGLOBTN4,KGLOBTN5,decode(KGLOBT10,1

                               ,'Y','N'),decode(KGLOBT10,1,KGLOBT02,null)from X$KGLOB where KGLOBTYP = 6 and KGLOBT11 = 1

V$_SEQUENCES                   select  SEQUENCE_OWNER , SEQUENCE_NAME , OBJECT# , ACTIVE_FLAG , REPLENISH_FLAG , WRAP_FLAG , NEXTVA

                               LUE , MIN_VALUE , MAX_VALUE , INCREMENT_BY , CYCLE_FLAG , ORDER_FLAG , CACHE_SIZE , HIGHWATER , BACK

                               GROUND_INSTANCE_LOCK , INSTANCE_LOCK_FLAGS from GV$_SEQUENCES where inst_id = USERENV('Instance')

--有一个内部视图可以访问,其实其基表就是 X$KGLOB.如果你查询dba_objects无法找到对应的视图的.也就是oracle没有放出来.

SYS@test> column SEQUENCE_NAME format a20

SYS@test> select * from sys.V$_SEQUENCES;

SEQUENCE_OWNER  SEQUENCE_NAME           OBJECT# A R W  NEXTVALUE  MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE  HIGHWATER B INSTANCE_LOCK_FLAGS

--------------- -------------------- ---------- - - - ---------- ---------- ---------- ------------ - - ---------- ---------- - -------------------

SYS             IDGEN1$                    1138 Y N N   58545651          1 1.0000E+28           50 N N       1000   58595601 N

SYS             AUDSES$                     361 Y N N    9662565          1 2000000000            1 Y N      10000    9672551 N

SCOTT           SEQ2                     301422 Y N N         45          1 1.0000E+28            1 N N         20         63 N

--如果删除 KGLOBT11 = 1 条件,与查询select  * from x$kglob a where kglobtyd='SEQUENCE';的数量一致.

--也可以查询视图v$access来确认正在使用的seq.

select * from v$access where type='SEQUENCE';