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
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
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
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
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
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
DECLARE v_seq NUMBER; BEGIN v_seq := item_seq.NEXTVAL; DBMS_OUTPUT.put_line('v_seq=' || v_seq); END;
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
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.