[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
SCOTT@test> select seq2.nextval from dual;
NEXTVAL
-----------------------------
2
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 21
--使用后LAST_NUMBER=21,也就是使用20完成后,下次从21开始.
2.测试异常关机的情况:
SYS@test> shutdown abort ;
ORACLE instance shut down.
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 21
SCOTT@test> select seq2.nextval from dual;
NEXTVAL
----------
21
SCOTT@test> select seq2.nextval from dual;
NEXTVAL
----------
22
--可以发现出现了跳号.
3.测试正常关机的情况:
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 23
--可以发现一个奇特的特性,正常关机是可以保证取号连续的.也就是讲在正常关机的情况下,系统会更新sys.seq$表.
SCOTT@test> select seq2.nextval from dual;
NEXTVAL
----------
23
SCOTT@test> select seq2.nextval from dual;
NEXTVAL
----------
24
4.刷新共享池呢?
SCOTT@test> alter system flush shared_pool;
System altered.
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 43
--可以推断刷新共享池以后,也会出现跳号情况.
SCOTT@test> select seq2.nextval from dual;
NEXTVAL
----------
43
SCOTT@test> select seq2.nextval from dual;
NEXTVAL
----------
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';
