In Oracle Database 12C new keywords SESSION, GLOBAL are available that can be specified during a sequence creation
CREATE SEQUENCE session_seq START WITH 1 INCREMENT BY 1 SESSION; CREATE SEQUENCE global_seq START WITH 1 INCREMENT BY 1 GLOBAL;
GLOBAL – creates standard sequence well known in previous release. This is the default.
SESSION – creates new type session sequence, which is a special type of sequence that is specifically designed to be used with global temporary tables that have session visibility. 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).
CACHE, NOCACHE,NOORDER are ignored when specified with SESSION calues
Example
New session sequence TEST_SEQ is created
SQL> connect tomasz@pora12c1 Enter password: Connected. SQL>CREATE SEQUENCE test_seq START WITH 1 INCREMENT BY 2 SESSION; sequence created. SQL>SELECT sequence_name, session_flag FROM user_sequences; SEQUENCE_NAME SESSION_FLAG -------------- ------------ TEST_SEQ Y
so it returns following data in session SID – 372
SQL> select sys_context('USERENV', 'SID') from dual; SYS_CONTEXT('USERENV','SID') ---------------------------- 372 SQL> SELECT test_seq.nextval FROM dual; NEXTVAL ---------- 1 SQL> SELECT test_seq.nextval FROM dual; NEXTVAL ---------- 3 SQL> SELECT test_seq.nextval FROM dual; NEXTVAL ---------- 5
If you connect to other session SID – 9 you can notice values are again starting from 1. Sequence state is independent in each session.
SQL> connect tomasz@pora12c1 Enter password: Connected. SQL> select sys_context('USERENV', 'SID') from dual; SYS_CONTEXT('USERENV','SID') ---------------------------- 9 SQL> SELECT test_seq.nextval FROM dual; NEXTVAL ---------- 1 SQL> SELECT test_seq.nextval FROM dual; NEXTVAL ---------- 3
Have a fun 🙂
Tomasz