[20131121]12c新特性Session sequences.txt
http://docs.oracle.com/cd/E16655_01/server.121/e17209/statements_6016.htm
SESSION Specify SESSION to create a session sequence, which is a special type of sequence that is specifically designed
to be used with global temporary tables that have session visibility. Unlike the existing regular sequences (referred to
as "global" sequences for the sake of comparison), a session sequence returns a unique range of sequence numbers only
within a session, but not across sessions. Another difference is that session sequences are not persistent. If a session
goes away, so does the state of the session sequences that were accessed during the session.
Session sequences must be created by a read-write database but can be accessed on any read-write or read-only databases
(either a regular database temporarily open read-only or a standby database).
在12c 可以建立sequence为session有效.自己做一个测试.
SQL> @ver
BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
SQL> create sequence seq1 start with 1 increment by 1 session;
Sequence created.
--打开会话1:
SQL> select seq1.nextval from dual connect by level
NEXTVAL
----------
1
2
3
4
5
6
7
8
9
10
10 rows selected.
--打开会话2:
SQL> select seq1.nextval from dual connect by level
NEXTVAL
----------
1
2
3
4
5
5 rows selected.
--可以发现仅仅在会话有效.
SQL> create sequence seq2 start with 1 increment by 1 ;
Sequence created.
SQL> column SEQUENCE_NAME format a20
SQL> column SESSION_FLAG format a16
SQL> select * from USER_SEQUENCES;
SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER PARTITION_COUNT S K
-------------------- ---------- ---------- ------------ - - ---------- ----------- --------------- - -
SEQ1 1 1.0000E+28 1 N N 20 1 Y N
SEQ2 1 1.0000E+28 1 N N 20 1 N N
SQL> desc USER_SEQUENCES;
Name Null? Type
---------------- -------- ------------------
SEQUENCE_NAME NOT NULL VARCHAR2(128)
MIN_VALUE NUMBER
MAX_VALUE NUMBER
INCREMENT_BY NOT NULL NUMBER
CYCLE_FLAG VARCHAR2(1)
ORDER_FLAG VARCHAR2(1)
CACHE_SIZE NOT NULL NUMBER
LAST_NUMBER NOT NULL NUMBER
PARTITION_COUNT NUMBER
SESSION_FLAG VARCHAR2(1)
KEEP_VALUE VARCHAR2(1)
--通过对比可以发现seq1的SESSION_FLAG=Y.真不知道oracle实现这个主要用途在那里?某个大客户的特殊需求....
--打开新会话,执行:
SQL> select seq1.nextval,seq2.nextval from dual connect by level
..
SQL> select * from USER_SEQUENCES;
SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER PARTITION_COUNT S K
-------------------- ---------- ---------- ------------ - - ---------- ----------- --------------- - -
SEQ1 1 1.0000E+28 1 N N 20 1 Y N
SEQ2 1 1.0000E+28 1 N N 20 41 N N
SQL> column audit$ noprint
SQL> select * from SYS.seq$ where obj# in (select object_id from user_objects where OBJECT_TYPE='SEQUENCE');
OBJ# INCREMENT$ MINVALUE MAXVALUE CYCLE# ORDER$ CACHE HIGHWATER FLAGS PARTCOUNT
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
91988 1 1 1.0000E+28 0 0 20 41 8 0
91986 1 1 1.0000E+28 0 0 20 1 72 0
--可以发现seq1的session sequence 最后的值LAST_NUMBER并没有变化.
--做一个简单的跟踪看看.
SQL> @10046on 12
old 1: alter session set events '10046 trace name context forever, level &1'
new 1: alter session set events '10046 trace name context forever, level 12'
Session altered.
SQL> select seq1.nextval from dual connect by level
...
SQL> @10046off
Session altered.
$ /home/oracle11g/bin/trimsql.sh a.trc
0001 0 #409596424>>>> select seq1.nextval from dual connect by level
0002 0 #409591896>>>> alter session set events '10046 trace name context off'
-- 看不出任何递归调用.
-- trimsql.sh 可以参考链接:
http://space.itpub.net/267265/viewspace-775398/
