ORA-02286: no options specified for alter sequence

ORA-02286: no options specified for ALTER SEQUENCE error occurs when you try to modify a sequence without specifying any parameters, or when you provide wrong parameters. You try to modify a sequence with an invalid argument or no parameter at all. Use the alter sequence command with the required configuration to change the sequence configuration values.

The alter sequence command is used to change the sequence’s present configuration. The alter sequence command may be used to change sequence configurations such as minimum value, maximum value, cache value, last value, increment value, and so on. If none of the configurations are given in the alter command, the sequence will fail with the error ORA-02286: no options specified for ALTER SEQUENCE.

The alter sequence is a DDL command which will allow to change the sequence current configuration value. The changed values will be persisted and can not be rolled back. If the alter sequence command is executed without any parameter, the DDL command could not change any sequence configurations.

Cause

Self-evident.

Action

The statement is meaningless without any options.



The Problem

The ALTER SEQUENCE command is used to modify an existing sequence configuration. The ALTER SEQUENCE is useless if no alternatives are offered in the ALTER SEQUENCE. The alter sequence command should modify at least one parameter in the sequence. Without any parameters, the alter sequence command has no effect. If no options are given in the sequence, the Oracle will throw an error.

In the following example, the alter sequence command is executed without any options given. The alter sequence command has no effect on the sequence’s configuration. In the second example, the alter sequence tries to change the maximum value to 50 and make the sequence cyclic. In the alter sequence command, the max value keyword is misspelt.

alter sequence mysequence;

OR 

alter sequence mysequence maxvaue 50 CYCLE;

Error

Error starting at line : 63 in command -
alter sequence mysequence
Error report -
ORA-02286: no options specified for ALTER SEQUENCE
02286. 00000 -  "no options specified for ALTER SEQUENCE"
*Cause:    Self-evident.
*Action:   The statement is meaningless without any options.


Solution 1

If the alter sequence command throws the error ORA-02286: no options specified for ALTER SEQUENCE, the alter sequence is executed without any options specified. Executing the alter sequence without any options is meaningless. The ALTER SEQUENCE command must include at least one parameter. The parameters such as minimum value, maximum value, cache value, cycle, start with, etc should be configured to modify the sequence.

alter sequence mysequence NOMAXVALUE;

Output

Sequence MYSEQUENCE altered.


Solution 2

If the alter sequence command is supplied with one or more options to execute, and you find an error, the sequence will include an incorrect option. Check the options in the alter sequence command. It’s possible that the option is wrong, or that it’s misspelt. The error will be fixed if you provide the proper option in the ALTER SEQUENCE command.

alter sequence mysequence maxvaue 50 CYCLE;

Error

Error starting at line : 63 in command -
alter sequence mysequence
Error report -
ORA-02286: no options specified for ALTER SEQUENCE
02286. 00000 -  "no options specified for ALTER SEQUENCE"
*Cause:    Self-evident.
*Action:   The statement is meaningless without any options.

Solution

alter sequence mysequence maxvalue 50 CYCLE;


Solution 3

The sequence details are important for understanding the current configurations of the sequence. The sequence minimum value, maximum value, increment value, cache value, last value, and so on must be required to determine how to fix the error. The following select query will provide you all the information you need about the sequence.

Run the sql below first to understand the current sequence setup. The necessary changes should be determined using the present sequence configuration. To change the sequence, use the alter sequence command and make the necessary changes.

select * from user_sequences where sequence_name = 'MYSEQUENCE';