"A Sequence is simply an automatic counter, which generates sequential numbers whenever required."
Oracle provides an object called a Sequence that can generate numeric values. The value generated can have a maximum of 38 digits.
A sequence can be defined to:
A sequence is an independent object and can be used with any table that requires its output.
The minimum information required for generating numbers using a sequence is:
CREATE SEQUENCE <Sequence Name> [INCREMENT BY <IntegerValue> START WITH <Integer Value> MAXVALUE <Integer Value> MINVALUE <Integer Value> CYCLE CACHE ]
Specifies the interval between sequence numbers. It can be any positive or negative value but not zero. If this clause is omitted, the default value is 1.
MAXVALUE And MINVALUE :
Specifies the maximum or minmum value that a sequence can generate.
Specifies the first sequence number to be generated. The default for an ascending sequence is the sequence minimum value (1) and for a descending sequence, it is the maximum value (-1).
Specifies that the sequence continues to generate repeat values after reaching either its maximum value.
Specifies how many values to generate in advance and to keep in memory for faster access.Minimum value is two for this option.
Oracle provides two pseudo columns NEXTVAL and CURVAL to access the values generated by the Sequence.
ALTER SEQUENCE <SequenceName> [INCREMENT BY <IntegerValue> MINVALUE <IntegerValue> ]
DROP SEQUENCE sequencename;