LAST_NUMBER Column in DBA_SEQUENCES

The LAST_NUMBER column in DBA_SEQUENCES shows you what a sequence's NEXTVAL would be if its cached values were lost. In the example below, a sequence is created with a CACHE of 13 values.

SQL> conn / as sysdba

Connected.

SQL> create sequence sequence1

  2  increment by 1

  3  start with 1

  4  cache 13

  5  /

Sequence created.

SQL> select sequence1.nextval from dual

  2  /

   NEXTVAL

----------

         1

SQL>

There are 13 values pre-calculated and stored in memory and the sequence increases by 1 each time its NEXTVAL is selected. The LAST_NUMBER is therefore 14:

SQL> select last_number from dba_sequences

  2  where sequence_name = 'SEQUENCE1'

  3  /

LAST_NUMBER

-----------

         14

SQL> select sequence1.nextval from dual

  2  /

   NEXTVAL

----------

         2

SQL>

Selecting the sequence's NEXTVAL does not make the sequence add an extra number to the cache so the LAST_NUMBER is still 14 afterwards:

SQL> select last_number from dba_sequences

  2  where sequence_name = 'SEQUENCE1'

  3  /

LAST_NUMBER

-----------

         14

SQL>

By flushing the shared pool, the sequence's cache is destroyed:

SQL> alter system flush shared_pool

  2  /

System altered.

SQL>

So the sequence's NEXTVAL is equal to LAST_NUMBER shown above:

SQL> select sequence1.nextval from dual

  2  /

   NEXTVAL

----------

        14

SQL>

... and a fresh cache of 13 terms is calculated so LAST_NUMBER is now 27:

SQL> select last_number from dba_sequences

  2  where sequence_name = 'SEQUENCE1'

  3  /

LAST_NUMBER

-----------

         27

SQL>

A SHUTDOWN NORMAL does not remove the sequence's cached values:

SQL> shutdown

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area   76518176 bytes

Fixed Size                   733984 bytes

Variable Size              67108864 bytes

Database Buffers            8388608 bytes

Redo Buffers                 286720 bytes

Database mounted.

Database opened.

SQL>

So the NEXTVAL is just 1 greater than the previous term:

SQL> select sequence1.nextval from dual

  2  /

   NEXTVAL

----------

        15

SQL>

However, the SHUTDOWN NORMAL does seem to check that the sequence still has 13 cached values as its LAST_NUMBER goes up by 1:

SQL> select last_number from dba_sequences

  2  where sequence_name = 'SEQUENCE1'

  3  /

LAST_NUMBER

-----------

         28

SQL>

A SHUTDOWN IMMEDIATE has the same effect:

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area   76518176 bytes

Fixed Size                   733984 bytes

Variable Size              67108864 bytes

Database Buffers            8388608 bytes

Redo Buffers                 286720 bytes

Database mounted.

Database opened.

SQL> select sequence1.nextval from dual

  2  /

   NEXTVAL

----------

        16

SQL> select last_number from dba_sequences

  2  where sequence_name = 'SEQUENCE1'

  3  /

LAST_NUMBER

-----------

         29

SQL>

However, a SHUTDOWN ABORT flushes the cache: 

SQL> shutdown abort

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area   76518176 bytes

Fixed Size                   733984 bytes

Variable Size              67108864 bytes

Database Buffers            8388608 bytes

Redo Buffers                 286720 bytes

Database mounted.

Database opened.

SQL>

The NEXTVAL goes up to the LAST_NUMBER value shown just above:

SQL> select sequence1.nextval from dual

  2  /

   NEXTVAL

----------

        29

SQL>

... and a fresh cache of terms is calculated for the sequence so its LAST_NUMBER goes up by 13 again:

SQL> select last_number from dba_sequences

  2  where sequence_name = 'SEQUENCE1'

  3  /

LAST_NUMBER

-----------

         42

SQL>