Use the CREATE SEQUENCE statement to create a sequence, which is a database object from which multiple users may generate unique integers. You can use sequences to automatically generate primary key values.
When a sequence number is generated, the sequence is incremented, independent of the transaction committing or rolling back. If two users concurrently increment the same sequence, then the sequence numbers each user acquires may have gaps, because sequence numbers are being generated by the other user. One user can never acquire the sequence number generated by another user. Once a sequence value is generated by one user, that user can continue to access that value regardless of whether the sequence is incremented by another user.Parameters in SEQUENCE:
1.INCREMENT BY- Tells the system how to increment the sequence. If it is positive, the values are ascending; if it is negative, the values are descending.
2.START WITH- Tells the system which integer to start with.
3.MINVALUE-Tells the system how low the sequence can go. For ascending sequences, it defaults to 1; for descending sequences, the default value is 10e27-1.
4.MAXVALUE- Tells the system the highest value that will be allowed. For descending sequences, the default is 1; for ascending sequences, the default is 10e27-1.
5.CYCLE-Causes the sequences to automatically recycle to minvalue when maxvalue is reached for ascending sequences; for descending sequences, it causes a recycle from minvalue back to maxvalue.
6.CACHE- Caches the specified number of sequence values into the buffers in the SGA. This speeds access, but all cached numbers are lost when the database is shut down. The default value is 20; maximum value is maxvalue-minvalue.
7.ORDER-Forces sequence numbers to be output in order of request. In cases where they are used for timestamping, this may be required. In most cases, the sequences numbers will be in order anyway, so ORDER will not be required. ORDER is necessary only to guarantee ordered generation if you are using Oracle with the Oracle Real Application Clusters option in parallel mode. If you are using exclusive mode, Oracle sequences numbers are always generated in order.
Example:
CREATE SEQUENCE emp_sequence_QueryHome
INCREMENT BY 1
START WITH 1
NOMAXVALUE
NOCYCLE
CACHE 10;
Reference: https://docs.oracle.com/cd/B14117_01/server.101/b10759/statements_6014.htm
