Alters a sequence. A sequence is a database object from which
multiple users can generate unique integers. You can use
sequences to automatically generate primary key values.
1 – Environment
You can use the ALTER SEQUENCE statement:
o In interactive SQL
o Embedded in host language programs to be precompiled
o As part of a procedure in an SQL module
o In dynamic SQL as a statement to be dynamically executed
2 – Format
ALTER SEQUENCE <sequence-name> -++--> <sequence-attributes> --------++->
|+--> RENAME TO <new-sequence-name> -+|
+--------------<----------------------+
sequence-attributes =
--+--> INCREMENT BY <numeric-value> ---------+->
+--> sequence-range -----------------------+
+--> CYCLE --------------------------------+
+--> NOCYCLE ------------------------------+
+--> CACHE <numeric-value> ----------------+
+--> NOCACHE ------------------------------+
+--> ORDER --------------------------------+
+--> NOORDER ------------------------------+
+--> RANDOMIZE ----------------------------+
+--> NORANDOMIZE --------------------------+
+--> RESTART WITH -------------------------+
+--> WAIT ---------------------------------+
+--> NOWAIT -------------------------------+
+--> DEFAULT WAIT -------------------------+
+--> COMMENT -+-------+--+-> '<string>' -+-+
+-> IS -+ +------ / <-----+
sequence-range =
-+-> MINVALUE -+-+--> <numeric-value> -+--+-->
+-> MAXVALUE -+ +--> TINYINT ----+ |
| +--> SMALLINT ---+ |
| +--> INTEGER ----+ |
| +--> BIGINT -----+ |
+-> NOMINVALUE --------------------------+
+-> NOMAXVALUE --------------------------+
3 – Arguments
3.1 – CACHE numeric-value
Syntax options:
CACHE numeric-value| NOCACHE
The CACHE clause specifies how many values of the sequence Oracle
Rdb should preallocate and keep in memory for faster access.
The mumeric value must be a value between 2 and 2147483647.
You cannot cache more values than will fit in a given cycle of
sequence numbers; thus, the maximum value allowed for the CACHE
clause must be less than the value resulting from the following
formula:
(MAXVALUE-MINVALUE)/ABS(INCREMENT)
You can alter the CACHE value if it is currently a value of 2
or higher. When you alter the CACHE value, existing users of the
sequence continue to use the original setting. You can use the
SET FLAGS 'SEQ_CACHE' statement to adjust the cache size for a
single process. See the See the SET_FLAGS statement for details.
If NOCACHE is currently enabled or the CACHE value is 1, you can
alter the CACHE value, but may have to wait until other users
of the sequence have released locks on it. (Note that CACHE 1 is
equivalent to NOCACHE.)
A cache for a given sequence is populated at the first request
for a number from that sequence, and whenever a value is
requested when the cache is empty. If a system failure occurs,
or when the cache is released any unfetched values will be
discarded. The maximum number of lost values is equal to the
current cache size. This may be the value specified by CACHE or
by the SET FLAGS SEQ_CACHE option.
The NOCACHE clause specifies that values will be allocated one
at a time. This will require more I/O to the Rdb root file than
using a CACHE value.
Note that even after you alter the CACHE value, users who
were using the sequence at the time you altered the CACHE will
continue to use the original setting.
3.2 – COMMENT_IS
Adds a comment about the sequence. SQL displays the text of the
comment when it executes a SHOW SEQUENCE statement. Enclose the
comment in single quotation marks ( ') and separate multiple
lines in a comment with a slash mark (/).
3.3 – CYCLE
Syntax options:
CYCLE | NOCYCLE
The CYCLE clause specifies that the sequence is to continue
generating values after reaching either the MINVALUE or MAXVALUE.
After an ascending sequence reaches the MAXVALUE, the sequence
starts again from its MINVALUE. After a descending sequence
reaches its MINVALUE, the sequence starts again at its MAXVALUE.
The NOCYCLE clause specifies that the sequence should not
continue generating values after reaching either its minimum
or maximum value. An error is generated if an attempt is made to
increment the sequence beyond its limits.
Note that even after you alter the CYCLE clause, those who
were using the sequence at the time you altered the CYCLE will
continue to use the original setting.
3.4 – INCREMENT BY numeric-value
Specifies the size of the increment and the direction (ascending
or descending) of the sequence. This numeric value must be in the
range -2147483648 through 2147483647, excluding 0. The absolute
value must be less than the difference between MAXVALUE and
MINVALUE. A negative value specifies a descending sequence; a
positive value specifies an ascending sequence. If the existing
value is positive, then the new value must also be positive.
Likewise, if the existing value is negative, then the new value
must also be negative. That is, you cannot change a sequence from
ascending to descending or from descending to ascending.
3.5 – MAXVALUE
Syntax options:
MAXVALUE numeric-value | NOMAXVALUE
The MAXVALUE clause specifies the maximum BIGINT value that the
sequence can generate. For an ascending sequence, the new maximum
value must be greater than or equal to the existing RDB$NEXT_
SEQUENCE_VALUE. For a descending sequence, the new maximum
value must be greater than or equal to the existing MAXVALUE.
This ensures that the MAXVALUE is not less than any currently
issued values. In addition, the numeric value must be between
-9223372036854775808 and 9223372036854775808. The MAXVALUE must
be greater than the value specified with the MINVALUE clause.
The NOMAXVALUE clause specifies that the maximum value for an
ascending sequence is 9223372036854775808 (minus the cache size),
and -1 for a descending sequence. The NOMAXVALUE clause is the
default.
3.6 – MAXVALUE integer-option
Syntax options:
MAXVALUE TINYINT
MAXVALUE SMALLINT
MAXVALUE INTEGER
MAXVALUE BIGINT
SQL allows the keyword TINYINT, SMALLINT, INTEGER and BIGINT
to follow MAXVALUE instead of a numeric value. This allows easy
range setting for sequences used with these data types. The value
supplied will be the largest positive value that can be assigned
to this data type.
3.7 – MINVALUE
Syntax options:
MINVALUE numeric-value | NOMINVALUE
The MINVALUE clause specifies the minimum signed quadword
(BIGINT) value that the sequence can generate. For an ascending
sequence, the new minimum value must be less than or equal
to the existing MINVALUE. For a descending sequence, the
new minimum value must be less than or equal to the existing
RDB$NEXT_SEQUENCE_VALUE. This prevents the minimum value
from being greater than any currently issued values. In
addition, the numeric value must be equal to or greater than -
9223372036854775808. The MINVALUE must be less than the value
specified with the MAXVALUE clause. The NOMINVALUE clause
specifies that the minimum value for an ascending sequence is 1,
and -9223372036854775808 (plus the cache size) for a descending
sequence.
The NOMINVALUE clause is the default.
3.8 – MINVALUE integer-option
Syntax options:
MINVALUE TINYINT
MINVALUE SMALLINT
MINVALUE INTEGER
MINVALUE BIGINT
SQL allows the keyword TINYINT, SMALLINT, INTEGER and BIGINT
to follow MINVALUE instead of a numeric value. This allows easy
range setting for sequences used with these data types. The value
supplied will be the smallest negative value that can be assigned
to this data type.
3.9 – ORDER
Syntax options:
ORDER | NOORDER
The ORDER clause specifies that sequence numbers are guaranteed
to be assigned in order for each requesting process, thus
maintaining a strict history of requests. The NOORDER clause
specifies that sequence numbers are not guaranteed to be
generated in order of request.
3.10 – RANDOMIZE
Syntax options:
RANDOMIZE | NORANDOMIZE
The RANDOMIZE clause specifies that the sequence numbers are to
be returned with a random value in the most significant bytes
of the BIGINT value. This allows unique values to be generated
that have a random distribution. When you specify the NORANDOMIZE
clause, sequence numbers are close in value to others created at
the same time.
The advantage of the RANDOMIZE clause is that updates to columns
of a stored index to which these values are written occur in
different locations in the index structure and so may improve
concurrent access for large indexes as leaf nodes in different
parts of the index can be updated independently. In contrast,
the sequence numbers generated when you specify the NORANDOMIZE
clause are likely to be close in numeric value to other sequence
values generated at the same time. This may cause index updates
to occur in the same or nearby index nodes, which may lead to
contention in one part of the sorted index.
The full range of values in the BIGINT value returned for the
sequence are used; therefore, the NOMAXVALUE and NOMINVALUE
clauses must be specified (or defaulted to) for the sequence
definition. The most significant bits of the BIGINT value
are set to a randomly generated positive value. A generated
distinct value is returned in the least significant 32 bits so
that uniqueness is guaranteed. If you also specify the CYCLE
clause, then only the least significant 32 bits are cycled. When
a query is performed on the column RDB$NEXT_SEQUENCE_VALUE in
the RDB$SEQUENCES table, only the generated value of the least
significant bits is returned, because the most significant bits
are not assigned until the NEXTVAL pseudocolumn is referenced.
If you specify RANDOMIZE, you cannot also specify ORDER,
MAXVALUE, or MINVALUE.
3.11 – RENAME_TO
Changes the name of the sequence being altered. See the RENAME
statement for further discussion. If the new name is the name of
a synonym then an error will be raised.
The new name must not exist as the name of an existing sequence,
synonym, table or view. You may not rename a system sequence.
The RENAME TO clause requires synonyms be enabled for this
database. Refer to the ALTER DATABASE SYNONYMS ARE ENABLED
clause. Note that these synonyms may be deleted if they are no
longer used by database definitions or applications.
3.12 – RESTART_WITH
The RESTART WITH clause allows the database administrator to
reset the sequence to a specified value. The value must be
within the range of MINVALUE and MAXVALUE. This command requires
exclusive access to the sequence. Once the ALTER SEQUENCE
statement is successfully committed, applications that use the
sequence will start with a value based on the restarted value.
NOTE
The TRUNCATE TABLE statement issued for a table with
an IDENTITY column implicitly executes an ALTER
SEQUENCE...RESTART WITH process on the sequence, applying
the MINVALUE if it is an ascending sequence, or MAXVALUE if
it is a descending sequence.
3.13 – sequence-name
The name of the sequence whose definition you want to change.
3.14 – WAIT
Syntax options:
WAIT | NOWAIT | DEFAULT WAIT
Specifies which wait state is used when a reference to NEXTVAL
is used. A reference to NEXTVAL for a sequence may require
synchronization with other users of the sequence. When you
specify DEFAULT WAIT the wait state (WAIT or NOWAIT) of the
current transaction is used. This may mean that no waiting is
performed during a NOWAIT transaction.
If you specify WAIT (the default) for the sequence, then
regardless of the wait state set for the current transaction, all
synchronization waits for the next value. This is the recommended
setting if the application uses NOWAIT transactions. The current
WAIT timeout interval defined for the transaction or database is
used.
If you specify NOWAIT for the sequence, then regardless of the
current transaction setting, all synchronization will not wait
for the next value.
Note that even after you alter the WAIT value, users who were
using the sequence at the time you altered WAIT will continue to
use the original setting.
4 – Examples
Example 1: Altering a sequence
SQL> -- Show current sequence definition:
SQL> --
SQL> SHOW SEQUENCE EMPIDS
EMPIDS
Sequence Id: 1
Initial Value: 1
Minimum Value: 1
Maximum Value: 9223372036854775787
Next Sequence Value: 1
Increment by: 1
Cache Size: 20
No Order
No Cycle
No Randomize
SQL> --
SQL> -- Alter the sequence.
SQL> --
SQL> ALTER SEQUENCE EMPIDS
cont> MINVALUE 0
cont> MAXVALUE 2000
cont> CACHE 30
cont> ORDER
cont> CYCLE;
SQL> --
SQL> -- Show new definition.
SQL> --
SQL> SHOW SEQUENCE EMPIDS
EMPIDS
Sequence Id: 1
Initial Value: 1
Minimum Value: (none)
Maximum Value: 2000
Next Sequence Value: 1
Increment by: 1
Cache Size: 30
Order
Cycle
No Randomize
Example 2: Reset the sequence to a specified value
SQL> show sequence NEW_EMPLOYEE_ID
NEW_EMPLOYEE_ID
Sequence Id: 1
Initial Value: 472
.
.
.
SQL>
SQL> alter sequence NEW_EMPLOYEE_ID
cont> restart with 500;
SQL>
SQL> show sequence NEW_EMPLOYEE_ID
NEW_EMPLOYEE_ID
Sequence Id: 1
Initial Value: 500
.
.
.
SQL>