New features of Oracle 12C-sequence

If you use global temporary tables and sequence, you may encounter some problems. Because global temporary tables are associated with sessions (or transactions in a session), sequence is related to the database level.

In 12C, you can create a sequence that is used only for a given session.

--When the global temporary table GTT is emptied in a session (such as commit), the value of sequence is not reset. The next call to the sequence will use its nextval value.

Sql> Connect scott/Tigersql>drop table GTT; SQL> CreateGlobaltemporary table GTT (ID number,seq_number number); SQL>Grant all on GTT to Tiger; SQL>drop sequence seq_session; SQL> Create sequence seq_session start with1session; SQL>Grant all on seq_session to Tiger; SQL> INSERT into GTT values (1, Seq_session.nextval); SQL> INSERT into GTT values (2, Seq_session.nextval); SQL>Select* fromSCOTT.GTT; ID Seq_number---------- ----------1          1         2          2SQL>commit; SQL>Select* fromscott.gtt;no rows Selectedsql>INSERT into GTT values (1, seq_session.nextval); insert into GTT values (2, Seq_session.nextval); SQL>Select* fromSCOTT.GTT; ID Seq_number---------- ----------1          3         2          4SQL>

Continue the test.
--end of session, sequence value will be reset

Sql>conn tiger/Tigersql>drop synonym GTT; SQL>drop synonym seq_session; SQL>create synonym GTT forSCOTT.GTT; SQL>create synonym Seq_session forscott.seq_session; SQL>Select* fromgtt;no rows Selectedsql>sql> INSERT INTO GTT values (1, Seq_session.nextval); sql> INSERT into GTT values (2, Seq_session.nextval);
-In 11g, the query result here is 5,6sql>Select* fromGTT; ID Seq_number---------- ----------1          1         2          2SQL>sql> commit; SQL>Select* fromgtt;no rows Selectedsql>Sql> Conn Scott/tigersql> INSERT INTO GTT values (1, Seq_session.nextval); sql> INSERT into GTT values (2, Seq_session.nextval);
-In 11g, the query result here is 7,8sql>Select* fromGTT; ID Seq_number---------- ----------1          1         2          2SQL>

New features of Oracle 12C-sequence