SEQUENCES

Sequences are iterations of numbers that we can define in a following way

CREATE SEQUENCE [schema.]sequencename
[INCREMENT BY number]
[START WITH number]
[MAXVALUE number | NOMAXVALUE]
[MINVALUE number | NOMINVALUE]
[CYCLE | NOCYCLE]
[CACHE number | NOCACHE]
[ORDER | NOORDER] ;

INCREMENT BY How much higher (or lower) than the last number issued should the next number be? Defaults to +1 but can be any positive number (or negative number for a descending sequence).
START WITH The starting point for the sequence: the number issued by the first selection. Defaults to 1 but can be anything.
MAXVALUE The highest number an ascending sequence can go to before generating an error or returning to its START WITH value. The default is no maximum.
MINVALUE The lowest number a descending sequence can go to before generating an error or returning to its START WITH value. The default is no minimum.
CYCLE Controls the behavior on reaching MAXVALUE or MINVALUE. The default behavior is to give an error, but if CYCLE is specified the sequence will return to its starting point and repeat.
CACHE For performance, Oracle can preissue sequence values in batches and cache them for issuing to users. The default is to generate and cache the next 20 values.
ORDER Only relevant for a clustered database: ORDER forces all instances in the cluster to coordinate incrementing the sequence, so that numbers issued are always in order even when issued to sessions against different instances.

To get the next number of iteration we would use

sequence_name.NEXTVAL

To get the previous value of the session we would use

sequence_name.CURRVAL

The CURRVAL of a sequence is the last value issued to the current session, not necessarily the last value issued. You cannot select the CURRVAL until after selecting the NEXTVAL.

About the cache function one should be careful as if the system failure occurs, cached values will be lost and then you need to use ALTER SEQUENCE command in order to return the iterator.

A COMMIT is not necessary to make the increment of a sequence permanent. It is permanent and made visible to the rest of the world the moment it happens.

Altering sequence

ALTER SEQUENCE sequencename
[INCREMENT BY number]
[START WITH number]
[MAXVALUE number | NOMAXVALUE]
[MINVALUE number | NOMINVALUE]
[CYCLE | NOCYCLE]
[CACHE number | NOCACHE]
[ORDER | NOORDER] ;

This ALTER command is the same as the CREATE command, with one exception: there is no way to set the starting value.

To adjust the cache value from default to improve performance of the preceding order entry example:

alter sequence order_seq cache 1000;

However, if you want to reset the sequence to its starting value, the only way is to drop it:
drop sequence order_seq;
and create it again.