About Oracle 12c Sequences

You can assign unique numbers, such as customer IDs, to columns in your database by using a sequence; you don’t need to create a special table and code to keep track of the unique numbers in use. You do this by using the CREATE SEQUENCE command, as shown here:

This creates a sequence that can be accessed during INSERT and UPDATE commands (also SELECT, although this is rare). Typically, the unique sequence value is created with a statement like the following:

The NEXTVAL attached to CUSTOMER_ID tells Oracle you want the next available sequence number from the CUSTOMER_ID sequence. This is guaranteed to be unique; Oracle will not give it to anyone else. To use the same number more than once (such as in a series of INSERTs into related tables), CURRVAL is used instead of NEXTVAL, after the first use. That is, using NEXTVAL ensures that the sequence table gets incremented and that you get a unique number, so you have to use NEXTVAL first. Once you’ve used NEXTVAL, that number is stored in CURRVAL for your use anywhere—until you use NEXTVAL again, at which point both NEXTVAL and CURRVAL change to the new sequence number.

If you use both NEXTVAL and CURRVAL in a single SQL statement, both will contain the value retrieved by NEXTVAL. Neither of these can be used in subqueries, as columns in the SELECT clause of a view, with DISTINCT, UNION, INTERSECT, or MINUS, or in the ORDER BY, GROUP BY, or HAVING clause of a SELECT statement.

You can also cache sequence values in memory for faster access, and you can make the sequence cycle back to its starting value once a maximum value is reached. In RAC environments, Oracle recommends caching 20,000 sequence values per instance to avoid contention during writes. For non-RAC environments, you should cache at least 1,000 values. Note that if you flush the shared pool portion of the instance, or you shut down and restart the database, any cached sequence values will be lost and there will be gaps in the sequence numbers stored in the database. See CREATE SEQUENCE in the Alphabetical Reference.