ALTER SEQUENCE

Purpose

This statement is used to change the increment, minimum and maximum values, cache size, and behavior of an existing sequence. The changes only affect subsequent sequence numbers.

Syntax

ALTER SEQUENCE [ schema. ] sequence_name
    [ INCREMENT BY int_value ]
    [ START WITH int_value ]
    [ MINVALUE int_value | NOMINVALUE ]
    [ MAXVALUE int_value | NOMAXVALUE ]
    [ CACHE int_value | NOCACHE ]
    [ ORDER | NOORDER ]
    [ CYCLE | NOCYCLE ]
    [ RESTART ]

Parameters

Examples

• Modify the maximum value of the seq1 sequence and specify that the increment value is recycled.

  -- Create the sequence first: CREATE SEQUENCE seq1;
  obclient> ALTER SEQUENCE seq1 MAXVALUE 1024 CYCLE;
  

• Change the maximum value and increment value of the seq3 sequence, enable CYCLE and ORDER, and specify NOCACHE to indicate that the sequence values are not preallocated.

  -- Create the sequence first: CREATE SEQUENCE seq3;
  obclient> ALTER SEQUENCE seq3 MAXVALUE 1500 INCREMENT BY 5 CYCLE NOCACHE ORDER;
  

• Reset the sequence value.

  -- Create the sequence first: CREATE SEQUENCE seq3;
  obclient> ALTER SEQUENCE seq3 RESTART;
  

• Set the starting value and reset the sequence.

  -- Create the sequence first: CREATE SEQUENCE seq3;
  obclient> ALTER SEQUENCE seq3 START WITH 3 RESTART;
  

• The RESTART option can be placed anywhere in the clause. The following two SQL statements for resetting the sequence value are equivalent.

  -- Create the sequence first: CREATE SEQUENCE seq3;
  obclient> ALTER SEQUENCE seq3 START WITH 3 RESTART MINVALUE -100 CYCLE;
  
  obclient> ALTER SEQUENCE seq3 RESTART START WITH 3 MINVALUE -100 CYCLE;