The ALTER SEQUENCE statement can be used to change the value of an existing sequence. The sequence can be in the current schema or a schema can be specified.

When altering a sequence of a multi-tenant table as a regular tenant, the FOR TENANT phrase is optional to modify its CURRVAL, and a regular tenant does not have the privileges to modify the global attributes of a sequence.

When altering the sequence of a multi-tenant table as a DBA or a super-tenant, the FOR TENANT phrase is mandatory for a DBA or a super-tenant to identify the tenant in the multi-tenant database that must be altered. A DBA or a super-tenant can alter all the sequence attributes of a multi-tenant table.

Syntax

The syntax of the ALTER SEQUENCE statement is:

ALTER SEQUENCE [schema_name]sequence_name[SET
     { CURRVAL value[ FOR TENANT tenant_name]| START WITH value| 
       INCREMENT BY value| MAXVALUE value | NOMAXVALUE | 
       MINVALUE value|NOMINVALUE | CYCLE | NOCYCLE }];

Only one attribute of a sequence can be altered at a time. Attributes START WITH, INCREMENT BY, MAXVALUE, MINVALUE, and CURRVAL can take either an INTEGER or BIGINT argument, depending on whether the sequence is a 32-bit or 64-bit sequence.

Example: ALTER SEQUENCE statement

The following example modifies a sequence that is used to generate unique customer numbers when a new customer is inserted into the table pub.customer.

ALTER SEQUENCE pub.customer
SET CURRVAL 57346147483647;

Example: Altering a sequence of a multi-tenant table

The following example when executed by a DBA or a super-tenant, modifies a sequence of a multi-tenant table by specifying a maximum value:

ALTER SEQUENCE pub.cust_num_seq SET MAXVALUE 99999;

Example: Altering a sequence of a multi-tenant table using CURRVAL

The following example when executed by a DBA or a super-tenant, modifies a sequence by specifying a CURRVAL for the tenant AsiaPecificCust of a multi-tenant table:

ALTER SEQUENCE pub.cust_num_seq SET CURRVAL 521 FOR TENANT mtAsiaPecificCust;

For more on the ALTER SEQUENCE statement, see OpenEdge Data Management: SQL Reference.