Oracle 12C - Session Sequences - Example / Usage

Let us explore session sequences in Oracle 12C in this tutorial with an example. Session sequences are  new concept in Oracle 12C that return unique values only within a session. An interesting usage of session sequences is with global temporary tables - where you can generate a unique value at a session level using this sequence. Before Oracle 12C, all sequences are considered to be "global" - the value persists across different sessions - However with Oracle 12C you have an option to specify a sequence to be used at session level. With this introduction, let us discuss some examples around session sequences.

1. How to Create a Session sequence in Oracle 12C?

You can create a session sequence in Oracle by using the keyword session in the statement to create the sequence. An example for creating a session sequence is provided below:

PDB1@ORCL> create sequence my_session_sequence
  2  start with 1000
  3  increment by 1
  4  SESSION
  5  /

Sequence created.

>

2. How to identify a session sequence?

There exists a column SESSION_FLAG in USER_SEQUENCES view. This column is set to a value of Y if the sequence is a session sequence. For normal (aka) global sequences this column is set to a value of N. You can try querying user_sequences for the example provided in (1) and see the output. You should see a value of Y for the session sequence.

PDB1@ORCL> 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

3. How to convert a session sequence to global sequence and vice versa?

You can use the alter sequence command to convert a regular sequence to a session sequence and vice versa. An example is provided below:

***********************
Convert a session sequence to global sequence
***************************

PDB1@ORCL> alter sequence MY_SESSION_SEQUENCE GLOBAL
  2  /

Sequence altered.

PDB1@ORCL> select session_flag from user_Sequences where sequence_name='MY_SESSION_SEQUENCE'
  2  /

S
-
N
****************************
convert a global sequence to session sequence
*******************************

PDB1@ORCL> alter sequence MY_SESSION_SEQUENCE SESSION
  2  /

Sequence altered.

PDB1@ORCL> select session_flag from user_Sequences where sequence_name='MY_SESSION_SEQUENCE'
  2  /

S
-
Y

4.Working Demonstration of Session Sequence

Although preferred for global temporary tables, let us try using it for a regular table and see session sequences in action. Create a test table using the statement below, and open two different sessions to see this in working.

PDB1@ORCL> create table test_session_sequence
  2  (
  3  MY_ID NUMBER,
  4  ADDED_BY VARCHAR2(10)
  5  )
  6  /

Table created.

PDB1@ORCL> insert into test_session_sequence values (my_session_sequence.nextval,'S1')
  2  /

1 row created.

PDB1@ORCL> /

1 row created.

PDB1@ORCL> /

1 row created.

PDB1@ORCL> select my_id,added_by from test_Session_sequence
  2  /

  MY_ID ADDED_BY
---------- ----------
      1000 S1
      1001 S1
      1002 S1

PDB1@ORCL> commit
  2  /

Commit complete.

***********************************************
Insert commands from session #2
************************************************
PDB1@ORCL> insert into test_session_sequence values (my_session_Sequence.nextval,'S2')
  2  /

1 row created.

PDB1@ORCL> /

1 row created.

PDB1@ORCL> /

1 row created.

PDB1@ORCL> select my_id,added_by from test_session_sequence
  2  /

     MY_ID ADDED_BY
---------- ----------
      1000 S2
      1001 S2
      1002 S2
      1000 S1
      1001 S1
      1002 S1

6 rows selected.

If you can see from the output above, you can find that both the sessions injected the same values 1000 to 1002 using this concept. This can also be extended inside PL SQL packages , stored procedures if required.

5. What happens to current value of a sequence when normal sequence is converted to session sequence and later reverted?

This is interesting. To try this, I executed the following statement below. The outcome is commented in the log that you can see for yourself.

*****************
Create a global sequence to start with
****************

PDB1@ORCL> create sequence test_conversion start with 100
  2  /

Sequence created.

*****************
Increment the sequence values and stop at 102
****************

PDB1@ORCL> select test_conversion.nextval from dual
  2  /

   NEXTVAL
----------
       100

PDB1@ORCL> /

   NEXTVAL
----------
       101

PDB1@ORCL> /

   NEXTVAL
----------
       102

*****************
Change sequence to a session sequence
****************
PDB1@ORCL> alter sequence test_conversion SESSION
  2  /

Sequence altered.

PDB1@ORCL> select test_conversion.currval from dual
  2  /

   CURRVAL
----------
       102
*****************
Increment value in session sequence
****************
       
PDB1@ORCL> select test_conversion.nextval from dual
  2  /

   NEXTVAL
----------
       103
*****************
Change sequence back to Global
****************
       
PDB1@ORCL> alter sequence test_conversion GLOBAL
  2  /

Sequence altered.

*****************
Observation: Current value on the sequence is set to the last value when the sequence was a session sequence. Note that this was all done in the same session. If you do the same from a different session, you can get different results based on the last value in the sequence.
****************

PDB1@ORCL> select test_conversion.currval from dual
  2  /

   CURRVAL
----------
       103