Example documentationSEQUENCES Locate this document in the navigation structure

 

Use of the system table DOMAIN.SEQUENCES

Prerequisites

You can use the demo data for the SQL tutorial. Start the Database Studio as database administrator MONA with the password RED and log on to demo database DEMODB: Logging On to a Database.

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

Activities

You can use Database Studio to enter and execute SQL statements. More information: Working with SQL Statements: Overview

Note the General Instructions for formulating SQL statements.

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