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