8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Oracle Sequences
This article demonstrates the common ways sequences can be used to generate synthetic or surrogate keys.
Related articles.
- Session Sequences in Oracle Database 12c Release 1 (12.1)
- Scalable Sequences in Oracle Database 18c
- AutoNumber And Identity Functionality (Pre 12c)
- Identity Columns in Oracle Database 12c Release 1 (12.1)
- DEFAULT Values for Table Columns : Enhancements in Oracle Database 12c Release 1 (12.1)
- Populating Master-Detail Foreign Key Values Using Sequences
Basic Management
This section will focus on basic management of sequences, including creating, modifying and removing sequences.
Privileges
The CREATE SEQUENCE
system privilege allows a user to create, alter and drop sequences defined in their own schema.
GRANT CREATE SEQUENCE TO my_user;
The following system privileges are also available, but they should not be granted as they allow the grantee to manipulate objects in all schemas, including built-in schemas.
GRANT CREATE ANY SEQUENCE, ALTER ANY SEQUENCE, DROP ANY SEQUENCE, SELECT ANY SEQUENCE TO my_user;
The owner of a sequence has full privileges on the sequence. Another user can be given access to the sequence by granting the SELECT object privilege.
GRANT SELECT ON my_user.my_seq TO another_user;
CREATE SEQUENCE
A sequence is a database object that is used to generate a unique integer, which is often used to populate a synthetic key. Sequences are created using the CREATE SEQUENCE
command. The vast majority of the time you will just specify a sequence name and use the defaults values for all sequence attributes, or maybe increase the CACHE
attribute above the default value of 20 to improve performance. If the schema isn't specified explicitly, it assumes you mean a sequence in the current schema.
CREATE SEQUENCE my_seq_1; CREATE SEQUENCE my_seq_2 CACHE 50;
The CREATE SEQUENCE documentation lists all the available sequence attributes. The example below uses MINVALUE
to set the starting point of the sequence, MAXVALUE
to set the end point of the sequence, CYCLE
to tell it to go back to the start once all available sequences are used and INCREMENT BY
to make it increase in steps of 10. The queries show the impact of this.
CREATE SEQUENCE my_seq_3 INCREMENT BY 10 MINVALUE 10 MAXVALUE 30 CYCLE; SQL> SELECT my_seq_3.NEXTVAL FROM dual; NEXTVAL ---------- 10 1 row selected. SQL> SELECT my_seq_3.NEXTVAL FROM dual; NEXTVAL ---------- 20 SQL> SELECT my_seq_3.NEXTVAL FROM dual; NEXTVAL ---------- 30 SQL> SELECT my_seq_3.NEXTVAL FROM dual; NEXTVAL ---------- 10 SQL>
By default a sequence is global, so its value is maintained across all sessions, from any user that has privilege to select from it. From Oracle 12c onward sequences can be defined as session-specific, so their current value is only relevant to the current session, and effectively reset for each new session.
Oracle 18c introduced the concept of scalable sequences.
ALTER SEQUENCE
Many of the sequence attributes can be altered after creation using the ALTER SEQUENCE
command. The full list of sequence attributes that can be altered are listed in the ALTER SEQUENCE documentation. If the schema isn't specified explicitly, it assumes you mean a sequence in the current schema. The following example alters some of the attributes of a sequence created in the previous section.
ALTER SEQUENCE my_seq_3 INCREMENT BY 1 MINVALUE 1 MAXVALUE 1000000 NOCYCLE;
DROP SEQUENCE
A sequence is dropped using the DROP SEQUENCE
command. If the schema isn't specified explicitly, it assumes you mean a sequence in the current schema.
DROP SEQUENCE my_seq_1; DROP SEQUENCE my_seq_2; DROP SEQUENCE my_schema.my_seq_3;
Views
The %_SEQUENCES
views are used to display information about sequences defined in the database.
USER_SEQUENCES
: All Sequences owned by the current user.ALL_SEQUENCES
: All sequences owned by the current user plus those the current user has privileges on.DBA_SEQUENCES
: All sequences in the database, or the current container in the multitenant architecture.CDB_SEQUENCES
: Displays all sequences in all containers when queried from the root container. Acts the same as theDBA_SEQUENCES
view when queries from any other container.
Usage in SQL and PL/SQL
Create a new sequence for some tests.
CREATE SEQUENCE my_seq;
The NEXTVAL
pseudocolumn displays the next available value for the sequence. Once a sequence number is selected, the session can access the current value repeatedly using the CURRVAL
pseudocolumn.
SQL> SELECT my_seq.NEXTVAL FROM dual; NEXTVAL ---------- 1 SQL> SELECT my_seq.NEXTVAL, my_seq.CURRVAL FROM dual; NEXTVAL CURRVAL ---------- ---------- 2 2 1 row selected. SQL> SELECT my_seq.CURRVAL FROM dual; CURRVAL ---------- 2 SQL> SELECT my_seq.NEXTVAL FROM dual; NEXTVAL ---------- 3 SQL>
It's safe to use the sequence repeatedly in an SQL statement.
SELECT my_seq.NEXTVAL FROM dual CONNECT BY level <= 5; NEXTVAL ---------- 4 5 6 7 8 SQL>
A sequence can be used directly in a DML statement. The following example uses the sequence in an insert statement to populate a column in the table.
CREATE TABLE t1 (id NUMBER); INSERT INTO t1 (id) VALUES (my_seq.NEXTVAL); INSERT INTO t1 (id) VALUES (my_seq.NEXTVAL); COMMIT; SELECT * FROM t1; ID ---------- 9 10 2 rows selected. SQL>
You can select the sequence value into a variable, allowing you to reference the value multiple times.
SET SERVEROUTPUT ON DECLARE l_seq NUMBER; BEGIN SELECT my_seq.NEXTVAL INTO l_seq FROM dual; INSERT INTO t1 (id) VALUES (l_seq); COMMIT; DBMS_OUTPUT.put_line('l_seq=' || l_seq); END; / l_seq=11 PL/SQL procedure successfully completed. SQL>
From Oracle 11g onward sequences can be used in assigned in PL/SQL. Under the hood there is still a query from dual, but it makes the code look neater.
DECLARE l_seq NUMBER; BEGIN l_seq := my_seq.NEXTVAL; INSERT INTO t1 (id) VALUES (l_seq); COMMIT; DBMS_OUTPUT.put_line('l_seq=' || l_seq); END; / l_seq=12 PL/SQL procedure successfully completed. SQL>
Remember, we could have used CURRVAL
rather than storing the value.
BEGIN INSERT INTO t1 (id) VALUES (my_seq.NEXTVAL); COMMIT; DBMS_OUTPUT.put_line('my_seq.CURRVAL=' || my_seq.CURRVAL); END; / my_seq.CURRVAL=13 PL/SQL procedure successfully completed. SQL>
You can see some examples of using sequences to populate primary key and foreign key columns in master-detail relationships here.
AutoNumber and Identity Columns
In database versions prior to Oracle 12c you had to mimic autonumber or identity column functionality using database triggers, as described here.
From Oracle 12c onward you can define proper identity columns, which under the hood use sequences. You can also use sequences as the default value for table columns.
- Identity Columns in Oracle Database 12c Release 1 (12.1)
- DEFAULT Values for Table Columns : Enhancements in Oracle Database 12c Release 1 (12.1)
For more information see:
- CREATE SEQUENCE
- ALTER SEQUENCE
- DROP SEQUENCE
- Session Sequences in Oracle Database 12c Release 1 (12.1)
- Scalable Sequences in Oracle Database 18c
- AutoNumber And Identity Functionality (Pre 12c)
- Identity Columns in Oracle Database 12c Release 1 (12.1)
- DEFAULT Values for Table Columns : Enhancements in Oracle Database 12c Release 1 (12.1)
- Populating Master-Detail Foreign Key Values Using Sequences
Hope this helps. Regards Tim...