Session level sequence

New session-level database sequences can now be created in 12c to support session-level sequence values. These sequence types are most applicable on global temporary tables with session levels.

Session-level sequencing produces a unique range of values that are restricted within the session, not beyond it. Once the session terminates, the state of the session sequence disappears

SQL> create sequence session_seq start with 1 increment by 1 session;

Sequence created.

SQL> select dbms_metadata.get_ddl('SEQUENCE','SESSION_SEQ','SYS') FROM DUAL;

DBMS_METADATA.GET_DDL('SEQUENCE','SESSION_SEQ','SYS')

CREATE SEQUENCE "SYS". "SESSION_SEQ" MINVALUE 1 MAXVALUE 999999999999999999

SQL> select session_seq.nextval from dual;

NEXTVAL 1 Open another window. ! [](https://s1.51cto.com/images/blog/201801/03/1a5988b3fcf0f27cbf8c02640235bf7a.png? x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=) It can be seen that the value of the sequence only affects the SESSION level. You can set a sequence to global or session level by ALTER SEQUENCE command. The following is to modify this sequence to global. The sequence value starts over from the initial value SQL> ALTER SEQUENCE session_seq GLOBAL;

Sequence altered.

SQL> select session_seq.nextval from dual;

NEXTVAL 1

SQL> /

NEXTVAL 2 Another one.

Changing a sequence from global to session-level via the ALTER SQEUENCE command differs from changing a sequence from session-level to global in that when you change a sequence from global to session-level, the values of the sequence are not reinitialized, but start with the previous sequence value of the current session, as detailed in the test below.

For session-level sequences, CACHE, NOCACHE, ORDER, or NOORDER statements are ignored.