12c - Session Level Sequences

In Oracle Database 12c, a new feature was introduced the Session level sequences.

Session level sequences will be used to produce unique values within a session. Once the session ends, the sequence is reset.

They are very used to generate Primary Keys values ​​in Global Temporary Tables.

SQL> CREATE SEQUENCE sequence_test
START WITH 1
INCREMENT BY 1
SESSION
/
Sequence created.


CREATE GLOBAL TEMPORARY TABLE Table_test (
id NUMBER DEFAULT sequence_test.nextval NOT NULL
, first_name VARCHAR2 (20)
, last_name VARCHAR2 (25)
)
ON COMMIT PRESERVE ROWS
/
Table created.


INSERT INTO Table_test (first_name, last_name)
SELECT first_name, last_name
FROM HR.EMPLOYEES
WHERE rownum <6
/
5 rows created.


SELECT *
FROM Table_test
/

EMPLOYEE_ID FIRST_NAME  LAST_NAME        
----------- -------------------- ------------------- ------
1 Joao  Silva 

2 Andre  Silva 

3  Pedro  Silva 

4  Mario  Silva 

5  Doe  Silva               
            
In another session:

INSERT INTO Table_test (first_name, last_name)
SELECT first_name, last_name
FROM HR.EMPLOYEES
WHERE rownum <10
/
9 rows created.


SELECT *
FROM Table_test
/

EMPLOYEE_ID         FIRST_NAME LAST_NAME
----------- -------------------- ----------- -------------- --------
1                                         Joao                    Silva
2                                         Andre                Silva

3                                         Pedro                  Silva

4                                         Mario                  Silva

5                                         Doe                      Silva

6                                         Ciclano                Silva

7                                         Beltrano             Silva                   
8                                         Jose                     Silva

9                                         Alex                     Silva                        
                         

Cache parameters NOCACHE, ORDER or NOORDER level are ignored by SESSION sequences.

You can change existing sequences:

SQL> ALTER SEQUENCE ... GLOBAL;

SQL> ALTER SESSION ... SEQUENCE;

Session level sequences must be created in a read / write database