SEQUENCES 

Prerequisites

You have generated the demo data for the SQL Tutorial.

Log on to the demo database instance DEMODB as user MONA.

Define and use sequences. Proceed as described in SQL Tutorial, Number Generators for Tables.

Examples

You can use the system table SEQUENCES to determine the following database information, among other things:

●     All sequences whose incremental value is not +1 and the threshold values of the sequences

SELECT owner, sequence_name, increment_by, min_value, max_value
  FROM DOMAIN.SEQUENCES
    WHERE increment_by <> 1

●     All sequences with a positive incremental value. The values are not assigned cyclically, and there are, at most, only 1000 free values remaining.

SELECT owner, sequence_name, last_number, max_value
  FROM DOMAIN.SEQUENCES
    WHERE increment_by > 0
      AND cycle_flag = 'N'
      AND max_value - last_number <= 1000

●     Current value of the sequence SEQU

SELECT last_number
  FROM DOMAIN.SEQUENCES
    WHERE schemaname = 'HOTEL'
      AND sequence_name = 'SEQU'

●     The last value of the sequence SEQU in the current database session

SELECT hotel.sequ.currval
FROM DUAL