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 /
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.