A sequence that starts at the starting point with every session that accesses the sequence…what is this good for?

Now, I could think at things like logging, where we could give every step of a session a ascending step_id, which would be stored in the log table along with the session_id to which the step belonged. Or we could have a transaction which creates a series of entries in the database and within the transaction those entries are numbered for whatever purpose. Generating Primary Keys for a Global Temporary Table would be another field where those kinds of sequences could be used.

---

Create a session level sequence

A session level sequence is generated using the new SESSION key word. This will restart the sequence for every session.

CREATE SEQUENCE session_seq
   START WITH   1
   INCREMENT BY 1
   SESSION
/ 

---

Usage

Example filling a global temporary table using a session level sequence as primary key generator:

CREATE SEQUENCE session_seq
 START WITH   1
 INCREMENT BY 1
 SESSION
/

Sequence created.

CREATE GLOBAL TEMPORARY TABLE employees_temp_table (
    employee_id  NUMBER DEFAULT session_seq.nextval NOT NULL
   ,first_name VARCHAR2(20)
   ,last_name VARCHAR2(25)
   )
   ON COMMIT PRESERVE ROWS
/

Table created.

REM ===========================================================================
REM Add rows to table
REM ===========================================================================
INSERT INTO employees_temp_table (first_name, last_name)
SELECT first_name, last_name
  FROM hr.employees
 WHERE rownum < 20
/

19 rows created.

REM ===========================================================================
REM Show the results
REM ===========================================================================
SELECT *
  FROM employees_temp_table
/

EMPLOYEE_ID FIRST_NAME           LAST_NAME
----------- -------------------- -------------------------
          1 Ellen                Abel
          2 Sundar               Ande
          3 Mozhe                Atkinson
          4 David                Austin
          5 Hermann              Baer
          6 Shelli               Baida
          7 Amit                 Banda
          8 Elizabeth            Bates
          9 Sarah                Bell
         10 David                Bernstein
         11 Laura                Bissot
         12 Harrison             Bloom
         13 Alexis               Bull
         14 Anthony              Cabrio
         15 Gerald               Cambrault
         16 Nanette              Cambrault
         17 John                 Chen
         18 Kelly                Chung
         19 Karen                Colmenares

19 rows selected.

If another session does the same thing, the employee_id will start at 1 again.