Syntax for creating sequence:
CREATE SEQUENCE schema_name. sequence_name
[INCREMENT BY interval]
[START WITH first_number]
[MAXVALUE max_value | NOMAXVALUE]
[MINVALUE min_value | NOMINVALUE]
[CYCLE | NOCYCLE ]
[CACHE cache_size | NOCACHE]
[ORDER |NOORDER];
While creating a sequence, Use the CREATE SEQUENCE command and add a sequence_name and that should be unique. The next increment by is used to show how much the sequence will increment at each move.
Then using START WITH, we start the sequence either in ascending or descending.
And MAXVALUE, NOMAXVALUE are maximum limits. with max value we can provide the maximum sequence value whereas nomax value is fixed, 10^27 for ascending sequence or -1 for descending sequence.
MINVALUE is to specify the minimum value of sequence. NOMINVALUE is fixed. For an ascending sequence it indicates a minimum value of 1 and -10^26 for a descending sequence.
To allow the sequence to generate value after it reaches the limit, then use CYCLE which is the minimum value for a descending sequence and max value for an ascending sequence. NOCYCLE is a default and it will be used when the sequence reaches the limit and you want to stop generating the next value.
And ORDER ensures that oracle generates sequence numbers in the order in which they are requested. if you don’t want Oracle to generate sequence numbers in the order of your requests, use NOORDER. This is a default setting.
