DBA Hub

📋Steps in this guide1/8

Oracle Sequences

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

oracle miscconfigurationintermediate
by OracleDba
22 views
1

Basic Management

This section will focus on basic management of sequences, including creating, modifying and removing sequences.
2

Privileges

The system privilege allows a user to create, alter and drop sequences defined in their own schema. 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. 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.

Code/Command (click line numbers to comment):

1
2
3
4
5
GRANT CREATE SEQUENCE TO my_user;

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

GRANT SELECT ON my_user.my_seq TO another_user;
3

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 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 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. The CREATE SEQUENCE documentation lists all the available sequence attributes. The example below uses to set the starting point of the sequence, to set the end point of the sequence, to tell it to go back to the start once all available sequences are used and to make it increase in steps of 10. The queries show the impact of this. 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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
CREATE SEQUENCE my_seq_1;
CREATE SEQUENCE my_seq_2 CACHE 50;

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>
4

ALTER SEQUENCE

Many of the sequence attributes can be altered after creation using the 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.

Code/Command (click line numbers to comment):

1
2
3
4
5
ALTER SEQUENCE my_seq_3
  INCREMENT BY 1
  MINVALUE 1
  MAXVALUE 1000000
  NOCYCLE;
5

DROP SEQUENCE

A sequence is dropped using the command. If the schema isn't specified explicitly, it assumes you mean a sequence in the current schema.

Code/Command (click line numbers to comment):

1
2
3
DROP SEQUENCE my_seq_1;
DROP SEQUENCE my_seq_2;
DROP SEQUENCE my_schema.my_seq_3;
6

Views

The views are used to display information about sequences defined in the database. - : All Sequences owned by the current user. - : All sequences owned by the current user plus those the current user has privileges on. - : All sequences in the database, or the current container in the multitenant architecture. - : Displays all sequences in all containers when queried from the root container. Acts the same as the view when queries from any other container.
7

Usage in SQL and PL/SQL

Create a new sequence for some tests. The 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 pseudocolumn. It's safe to use the sequence repeatedly in an SQL statement. 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. You can select the sequence value into a variable, allowing you to reference the value multiple times. 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. Remember, we could have used rather than storing the value. You can see some examples of using sequences to populate primary key and foreign key columns in master-detail relationships here. - Populating Master-Detail Foreign Key Values Using Sequences

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
CREATE SEQUENCE my_seq;

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>

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

   NEXTVAL
----------
         4
         5
         6
         7
         8

SQL>

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>

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>

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>

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>
8

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...

Comments (0)

Please to add comments

No comments yet. Be the first to comment!