Create Sequence-Oracle Database 12c

Create Sequence-Oracle Database 12c
  • 1
    Mar

Create Sequence-Oracle Database 12c

twittergoogle_pluslinkedin

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

Global Temporary Table


Oracle web site
https://docs.oracle.com/database/121/SQLRF/statements_6017.htm#SQLRF01314