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.
