Oracle Sequence

What is a sequence

A sequence is a list of integers in which their orders are important. For example, the (1,2,3,4,5) and (5,4,3,2,1) are totally different sequences even though they have the same members.

Creating a sequence

The CREATE SEQUENCE statement allows you to create a new sequence object in your own schema.

For example, this statement uses the CREATE SEQUENCE statement to create a new sequence object named item_seq:

CREATE SEQUENCE item_seq;

You use the sequence object to generate a sequence of unique integers, mostly for surrogate key columns.

Note that Oracle 12c automatically generates a sequence object associated with the identity column of the table.

Using a sequence

To access the next available value for a sequence, you use the NEXTVAL pseudo-column:

SELECT item_seq.NEXTVAL
FROM dual;

   NEXTVAL
----------
         1

Once, you acquire the sequence number through the NEXTVAL pseudo-column, you can access it repeatedly using the CURRVAL pseudo-column:

SELECT item_seq.CURRVAL
FROM dual;

   CURRVAL
----------
         1

The following statement uses the item_seq sequence repeatedly in an SQL statement:

SELECT item_seq.NEXTVAL
FROM   dual
CONNECT BY level <= 5;

   NEXTVAL
----------
         2
         3
         4
         5
         6

This example uses the item_seq sequence in the INSERT statements to populate values for the item_id column of the items table:

CREATE TABLE items(
    item_id NUMBER
);

INSERT INTO items(item_id) VALUES(item_seq.NEXTVAL);
INSERT INTO items(item_id) VALUES(item_seq.NEXTVAL);

COMMIT;

SELECT item_id FROM items;

Here is the output:

   ITEM_ID
----------
         7
         8

From Oracle 11g onward, you can use sequences in PL/SQL. Behind the scenes, Oracle still uses a query from the dual table, but it makes the code cleaner:

DECLARE
    v_seq NUMBER;
BEGIN
    v_seq  := item_seq.NEXTVAL;
    DBMS_OUTPUT.put_line('v_seq=' || v_seq);
END;

v_seq=9

Modifying a sequence

To modify the attributes and behavior of an existing sequence object, you use the ALTER SEQUENCE statement.

The following example uses the ALTER SEQUENCE to set the maximum value for the item_seq to 100:

ALTER SEQUENCE item_seq MAXVALUE 100;

Removing a sequence

To remove an existing sequence from the database, you use the DROP SEQUENCE statement. The following example uses the DROP SEQUENCE statement to delete the item_seq sequence:

DROP SEQUENCE item_seq;

Oracle sequence privileges

Oracle provides the CREATE SEQUENCE system privilege to allow you to create, alter, and drop sequences.

This statement grants a user the CREATE SEQUENCE privilege:

GRANT CREATE SEQUENCE 
TO user_name;

In addition, Oracle provides the following privileges that allow you to manipulate sequences in all schemas, including the built-in ones:

• CREATE ANY SEQUENCE
• ALTER ANY SEQUENCE
• DROP ANY SEQUENCE
• SELECT ANY SEQUENCE

Therefore, you should consider seriously before executing the following command:

GRANT CREATE ANY SEQUENCE, 
    ALTER ANY SEQUENCE, 
    DROP ANY SEQUENCE, 
    SELECT ANY SEQUENCE 
TO user_name;

If you are the owner of the sequence, you will have the full privileges on the sequence. To grant another user access to a sequence, you can grant the SELECT object privilege to that user as shown in the following command:

GRANT SELECT ON user_name.sequence_name 
TO another_user;

More on sequences

• CREATE SEQUENCE – create a new sequence object in the database.
•  ALTER SEQUENCE – modify the attributes and behaviors of an existing sequence.
•  DROP SEQUENCE – drop an existing sequence.

In this tutorial, you have learned about Oracle sequence including creating, using, modifying, and removing a sequence.