Create Sequence-Oracle Database 12c
Database version : from Oracle Database 12cR1
Create sequence with session or global option
When a sequence number is generated, the sequence is incremented, independent of the transaction committing or rolling back.
SESSION option makes opportunity to create sequence that can used in association with global temporary table then its visibility is just for session.
The following options CACHE, NOCACHE, ORDER, or NOORDER are ignored when SESSION is declared.
1) statement
SQL> CREATE GLOBAL TEMPORARY TABLE glb_tmp_table (a number);
Table created.
SQL> CREATE SEQUENCE sq_sess SESSION;
Sequence created.
2) Script execution
SQL> INSERT INTO glb_tmp_table VALUES (sq_sess.NEXTVAL);
1 row created.
SQL> INSERT INTO glb_tmp_table VALUES (sq_sess.NEXTVAL);
1 row created.
SQL> SELECT * FROM glb_tmp_table;
A
———-
1
2
3) Run the script above in other session. It shows the sequence start from first number:
SQL> INSERT INTO glb_tmp_table VALUES (sq_sess.NEXTVAL);
1 row created.
SQL> INSERT INTO glb_tmp_table VALUES (sq_sess.NEXTVAL);
1 row created.
SQL> SELECT * FROM glb_tmp_table;
A
———-
1
2
Specify GLOBAL to create a global, or regular, sequence. This is the default.
Here we are going to alter existing sequence to be a regular (“global”) sequence;
alter sequence sq_sess GLOBAL;
or, as usual create the new one:
CREATE SEQUENCE number_seq
START WITH 10
INCREMENT BY 1
NOCACHE
NOCYCLE
GLOBAL ;
Excursus:
Area Etica web site
Oracle web site
https://docs.oracle.com/database/121/SQLRF/statements_6017.htm#SQLRF01314