8i | 9i | 10g | 11g | 12c | 13c | 18c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » Misc » Here

Oracle Sequences

This article demonstrates the common ways sequences can be used to generate synthetic or surrogate keys.

Related articles.

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.

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.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.