Oracle Sequences
Version 21c

General Information
Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
Be sure to view the full listing of monographs in Morgan's Library
Data Dictionary Objects
ALL_SEQUENCES DBA_SEQUENCES USER_SEQUENCES
CDB_SEQUENCES SEQ$  
Related System Privileges
ALTER ANY SEQUENCE CREATE SEQUENCE SELECT ANY SEQUENCE
CREATE ANY SEQUENCE DROP ANY SEQUENCE  
Sequence Alternative The alternative to sequences used in other RDBMS products is autonumbering and keeping the current number in a table. Both of these other methods demand serialization as they can only dispense one number at a time.
Table example:

CREATE TABLE seqnum (
next_number NUMBER(1);


1. Lock the seqnum table for your transaction
2. SELECT next_number FROM seqnum;
3. UPDATE seqnum SET next_number=next_number+1;
4. Unlock the seqnum table for the next transaction
Tables For Sequence Demos conn uwclass/uwclass@pdbdev

CREATE TABLE campus_site (
site_id           NUMBER(4),
organization_name VARCHAR2(40),
campus_name       VARCHAR2(30),
address_id        NUMBER(10));

CREATE TABLE division (
division_id   NUMBER(5),
site_id       NUMBER(4),
division_name VARCHAR2(40),
address_id    NUMBER(10));

CREATE TABLE department (
department_id   NUMBER(5),
division_id     NUMBER(5),
department_name VARCHAR2(40),
address_id      NUMBER(10));

CREATE TABLE seq_test (
test NUMBER(10));
 
Create Sequence
Full Create Sequence Syntax CREATE SEQUENCE <sequence_name>
INCREMENT BY <integer>
START WITH <integer>
MAXVALUE <integer> / NOMAXVALUE
MINVALUE <integer> / NOMINVALUE
CYCLE / NOCYCLE
CACHE <#> / NOCACHE
ORDER / NOORDER;
Create Sequence Simplest Form CREATE SEQUENCE <sequence_name>;
CREATE SEQUENCE seq_campus_site_id;

SELECT seq_campus_site_id.NEXTVAL FROM dual;
/
/
Simple Autonumber With Sequence INSERT INTO <table_name>
(<column_name>)
VALUES
(<sequence_name>.NEXTVAL);
INSERT INTO campus_site
(site_id, organization_name, campus_name)
VALUES
(seq_campus_site_id.NEXTVAL, 'Univ. of Washington', 'Main Seattle');

SELECT *
FROM campus_site;

INSERT INTO campus_site
(site_id, organization_name, campus_name)
VALUES
(seq_campus_site_id.NEXTVAL, 'Univ. of Washington', 'Bothell');

SELECT *
FROM campus_site;
Simple Autonumber With Sequence Into Two Tables

Thanks Milo van der Leij for the correction
INSERT INTO <table_name>
(<column_name>)
VALUES
(<sequence_name>.CURRVAL);
CREATE SEQUENCE seq_division_id;

INSERT INTO campus_site
(site_id, organization_name, campus_name)
VALUES
(seq_campus_site_id.NEXTVAL, 'Univ. of Washington', 'Tacoma');

INSERT INTO division
(division_id, site_id, division_name)
VALUES
(seq_division_id.NEXTVAL, seq_campus_site_id.CURRVAL, 'Engineering');

SELECT *
FROM campus_site;

SELECT *
FROM division;
Simple Transaction Number For Audit Demoing START WITH and a caution with CURRVAL CREATE SEQUENCE <sequence_name> START WITH <integer>;
CREATE SEQUENCE seq_audit_tx START WITH 42;

INSERT INTO campus_site
(site_id, organization_name, campus_name)
VALUES
(seq_audit_tx.NEXTVAL, 'Univ. of Washington', 'Everett');

INSERT INTO division
(division_id, site_id, division_name)
VALUES
(seq_audit_tx.NEXTVAL, seq_audit_tx.CURRVAL, 'Science');

INSERT INTO department
(department_id, division_id, department_name)
VALUES
(seq_audit_tx.NEXTVAL, seq_audit_tx.CURRVAL, 'Astronomy');

SELECT * FROM campus_site;
SELECT * FROM division;
SELECT * FROM department;

ROLLBACK;

INSERT INTO campus_site
(site_id, organization_name, campus_name)
VALUES
(seq_audit_tx.NEXTVAL, 'Univ. of Washington', 'Everett');

INSERT INTO division
(site_id, division_id, division_name)
VALUES
(seq_audit_tx.CURRVAL, seq_audit_tx.NEXTVAL, 'Science');

INSERT INTO department
(division_id, department_id, department_name)
VALUES
(seq_audit_tx.CURRVAL, seq_audit_tx.NEXTVAL, 'Astronomy');

SELECT * FROM campus_site;
SELECT * FROM division;
SELECT * FROM department;
INCREMENT BY CREATE SEQUENCE <sequence_name> INCREMENT BY <positive integer>;
CREATE SEQUENCE seq_inc_by_two INCREMENT BY 2;

INSERT INTO seq_test VALUES (seq_inc_by_two.NEXTVAL);
/
/

SELECT * FROM seq_test;

CREATE SEQUENCE seq_inc_by_ten INCREMENT BY 10;

INSERT INTO seq_test VALUES (seq_inc_by_ten.NEXTVAL);
/
/

SELECT * FROM seq_test;

ALTER TABLE seq_test ADD test2 NUMBER(10);

desc seq_test

INSERT INTO seq_test
(test, test2)
VALUES
(seq_inc_by_ten.NEXTVAL, seq_inc_by_ten.NEXTVAL);

SELECT * FROM seq_test;

INSERT INTO seq_test
(test, test2)
VALUES
(seq_inc_by_ten.NEXTVAL, seq_inc_by_ten.CURRVAL);

SELECT * FROM seq_test;
Reverse DECREMENT BY CREATE SEQUENCE <sequence_name>
MAX VALUE <integer value>
INCREMENT BY <negative integer>;
CREATE SEQUENCE seq_reverse INCREMENT BY 5;

ALTER TABLE seq_test DROP COLUMN test2;

INSERT INTO seq_test VALUES (seq_reverse.NEXTVAL);
/
/
/

SELECT * FROM seq_test;

DROP SEQUENCE seq_reverse;

CREATE SEQUENCE seq_reverse MAXVALUE 150
START WITH 150 INCREMENT BY -5;

INSERT INTO seq_test VALUES (seq_reverse.NEXTVAL);
/
/
/

SELECT * FROM seq_test;
MAXVALUE Demo CREATE SEQUENCE <sequence_name> START WITH <integer>
MAXVALUE <integer>;
CREATE SEQUENCE seq_maxval START WITH 1 MAXVALUE 5;

INSERT INTO seq_test VALUES (seq_maxval.NEXTVAL);
/
/
/

SELECT * FROM seq_test;

INSERT INTO seq_test VALUES (seq_maxval.NEXTVAL);

SELECT * FROM seq_test;

INSERT INTO seq_test VALUES (seq_maxval.NEXTVAL);
CACHE Demo CREATE SEQUENCE <sequence_name> CACHE <integer>;
CREATE SEQUENCE seq_cache CACHE 100;

SELECT sequence_name, last_number
FROM user_sequences;

SELECT seq_cache.NEXTVAL FROM dual;

SELECT sequence_name, last_number
FROM user_sequences;

SELECT seq_cache.NEXTVAL FROM dual;
/

SELECT sequence_name, last_number
FROM user_sequences;

conn sys@pdbdev as sysdba

shutdown abort;

startup

conn uwclass/uwclass@pdbdev

SELECT sequence_name, last_number
FROM user_sequences;

SELECT seq_cache.NEXTVAL FROM dual;
CACHE Size Demo

The default sequence cache in Oracle is 20 which is a ridiculous value. Far too small for anything and everything. The demo at right shows one aspect of the contention created through accepting this default.
conn sys@pdbdev as sysdba

SELECT object_id FROM dba_objects WHERE object_name = 'AUDSES$';

OBJECT_ID
----------
      548


DECLARE
 v_rid rowid;
 v_type number;
 v_obj number;
 v_rfno number;
 v_rno number;
 v_bno number;
BEGIN
  SELECT rowid INTO v_rid
  FROM sys.seq$
  WHERE obj# = 548;

  dbms_rowid.rowid_info(v_rid, v_type, v_obj, v_rfno, v_bno, v_rno);
  dbms_output.put_line('v_rfno= "'||v_rfno||'"');
  dbms_output.put_line('v_bno= "'||v_bno||'"');
END;
/
v_rfno= "1"
v_bno= "1161"

PL/SQL procedure successfully completed.


DECLARE
 v_rid ROWID;
 v_type NUMBER;
 v_obj NUMBER;
 v_rfno NUMBER;
 v_rno NUMBER;
 v_bno NUMBER;

 CURSOR so_cur IS
 SELECT object_name, object_id, cache_size
 FROM dba_objects do, dba_sequences ds
 WHERE do.object_type = 'SEQUENCE'
 AND do.owner = 'SYS'
 AND ds.sequence_owner = 'SYS'
 AND do.object_name = ds.sequence_name
 ORDER BY 3,2 DESC;
BEGIN
  FOR so_rec IN so_cur LOOP
    SELECT rowid INTO v_rid
    FROM sys.seq$
    WHERE obj# = so_rec.object_id;

    dbms_rowid.rowid_info(v_rid, v_type, v_obj, v_rfno, v_bno, v_rno);
    dbms_output.put_line(RPAD(so_rec.object_name,30) || ' cache size: ' || LPAD(so_rec.cache_size,5) || ' stored at: v_rfno='||v_rfno || ' and v_bno= ' || v_bno);
  END LOOP;
END;
/
UTL_RECOMP_SEQ                 cache size:     0 stored at: v_rfno=1 and v_bno= 1163
AWREPORT_S$                    cache size:     0 stored at: v_rfno=1 and v_bno= 1163
AWXML_S$                       cache size:     0 stored at: v_rfno=1 and v_bno= 1163
AWCREATE10G_S$                 cache size:     0 stored at: v_rfno=1 and v_bno= 1163
AWCREATE_S$                    cache size:     0 stored at: v_rfno=1 and v_bno= 1163
AWMD_S$                        cache size:     0 stored at: v_rfno=1 and v_bno= 1163
EXPRESS_S$                     cache size:     0 stored at: v_rfno=1 and v_bno= 1163
HS_BULK_SEQ                    cache size:     0 stored at: v_rfno=1 and v_bno= 1162
STREAMS$_RULE_NAME_S           cache size:     0 stored at: v_rfno=1 and v_bno= 1162
FGR$_NAMES_S                   cache size:     0 stored at: v_rfno=1 and v_bno= 1162
...
AQ$_PUBLISHER_SEQUENCE         cache size:  1000 stored at: v_rfno=1 and v_bno= 1161
AQ$_RULE_SEQUENCE              cache size:  1000 stored at: v_rfno=1 and v_bno= 1161
AQ$_RULE_SET_SEQUENCE          cache size:  1000 stored at: v_rfno=1 and v_bno= 1161
AQ$_IOTENQTXID                 cache size:  1000 stored at: v_rfno=1 and v_bno= 1161
IDGEN1$                        cache size:  1000 stored at: v_rfno=1 and v_bno= 1161
PSINDEX_SEQ$                   cache size:  1000 stored at: v_rfno=1 and v_bno= 1161
DBFS_SFS$_FSSEQ                cache size:  8192 stored at: v_rfno=1 and v_bno= 1162
CDC_RSID_SEQ$                  cache size: 10000 stored at: v_rfno=1 and v_bno= 1161
AUDSES$                        cache size: 10000 stored at: v_rfno=1 and v_bno= 1161
ORA_TQ_BASE$                   cache size: 10000 stored at: v_rfno=1 and v_bno= 1161

PL/SQL procedure successfully completed.

/* Using the same PL/SQL block to translate a ROWID into file# and block# value, we find that all of the sequences reside within a small number of blocks. So, any sequence needing to be refreshed from a less-busy RAC instance is going to wait for one of these blocks because the more-busy RAC node will be dominating access to that block. */
CYCLE Demo CREATE SEQUENCE <sequence_name> START WITH <integer>
MAXVALUE <integer> CYCLE;
-- this will fail
CREATE SEQUENCE
seq_cycle START WITH 1 MAXVALUE 5 CYCLE;
-- default cache is 20

-- this will succeed
CREATE SEQUENCE seq_cycle START WITH 1 MAXVALUE 5 CYCLE CACHE 4;

TRUNCATE TABLE seq_test;

INSERT INTO seq_test VALUES (seq_cycle.NEXTVAL);
/
/
/
/
/
/

SELECT * FROM seq_test;
ORDER Demo
Do not use this option unless written business rules make doing so mandatory
CREATE SEQUENCE <sequence_name> START WITH 1 ORDER;
CREATE SEQUENCE seq_order START WITH 1 ORDER;
 
Alter Sequence
Change Increment ALTER SEQUENCE <sequence_name> INCREMENT BY <integer>;
SELECT increment_by
FROM user_sequences
WHERE sequence_name = 'SEQ_INC_BY_TEN';

ALTER SEQUENCE seq_inc_by_ten INCREMENT BY 20;

SELECT increment_by
FROM user_sequences
WHERE sequence_name = 'SEQ_INC_BY_TEN';
Change Max Value ALTER SEQUENCE <sequence_name> MAX VALUE <integer>;
SELECT max_value
FROM user_sequences
WHERE sequence_name = 'SEQ_MAXVAL';

ALTER SEQUENCE
seq_maxval MAXVALUE 10;

SELECT max_value
FROM user_sequences
WHERE sequence_name = 'SEQ_MAXVAL';
Change Cycle ALTER SEQUENCE <sequence_name> <CYCLE | NOCYCLE>;
SELECT cycle_flag
FROM user_sequences
WHERE sequence_name = 'SEQ_CYCLE';

ALTER SEQUENCE
seq_cycle NOCYCLE;

SELECT cycle_flag
FROM user_sequences
WHERE sequence_name = 'SEQ_CYCLE';
Change Cache ALTER SEQUENCE <sequence_name> CACHE <integer> | NOCACHE;
SELECT cache_size
FROM user_sequences
WHERE sequence_name = 'SEQ_CACHE';

ALTER SEQUENCE
seq_cache NOCACHE;

SELECT cache_size
FROM user_sequences
WHERE sequence_name = 'SEQ_CACHE';
Change Order ALTER SEQUENCE <sequence_name> <ORDER | NOORDER>;
SELECT order_flag
FROM user_sequences
WHERE sequence_name = 'SEQ_ORDER';

ALTER SEQUENCE seq_order NOORDER;

SELECT order_flag
FROM user_sequences
WHERE sequence_name = 'SEQ_ORDER';
 
Drop Sequence
Drop Sequence DROP SEQUENCE <sequence_name>;
DROP SEQUENCE seq_cache;
 
Using A Sequence
In an Insert CREATE TABLE t (
col1 NUMBER(5),
col2 NUMBER(5));

CREATE SEQUENCE seq;

INSERT INTO t
(col1, col2)
VALUES
(seq.NEXTVAL, seq.CURRVAL);

/
/

SELECT * FROM t;
PL/SQL prior to 11g set serveroutput on

DECLARE
 i NATURAL;
 j NATURAL;
BEGIN
  SELECT seq.NEXTVAL, seq.CURRVAL
  INTO i, j
  FROM dual;

  dbms_output.put_line(i);
  dbms_output.put_line(j);
END;
/

/
PL/SQL in 11g and above set serveroutput on

BEGIN
  dbms_output.put_line(seq.NEXTVAL);
  dbms_output.put_line(seq.CURRVAL);
END;
/

/
 
Sequence Resets
By finding out the current value of the sequence and altering the increment by to be negative that number and selecting the sequence once -- the sequence can be reset to 0.

If any session attempts to use the sequence while this is happening an ORA-08004 error will be generated.
CREATE SEQUENCE seq;
SELECT seq.NEXTVAL FROM dual;
SELECT seq.NEXTVAL FROM dual;
SELECT seq.NEXTVAL FROM dual;

COLUMN S new_val inc;
SELECT seq.NEXTVAL S FROM dual;

ALTER SEQUENCE seq INCREMENT BY -&inc MINVALUE 0;
SELECT seq.NEXTVAL S FROM dual;

ALTER SEQUENCE seq increment by 1;
SELECT seq.NEXTVAL FROM dual;
/
/
Stored Procedure Method CREATE OR REPLACE PROCEDURE reset_sequence (
seq_name IN VARCHAR2, startvalue IN PLS_INTEGER) AS
 cval   INTEGER;
 inc_by VARCHAR2(25);
BEGIN
  EXECUTE IMMEDIATE 'ALTER SEQUENCE ' ||seq_name||' MINVALUE 0';

  EXECUTE IMMEDIATE 'SELECT ' ||seq_name ||'.NEXTVAL FROM dual'
  INTO cval;

  cval := cval - startvalue + 1;
  IF cval < 0 THEN
    inc_by := ' INCREMENT BY ';
    cval:= ABS(cval);
  ELSE
    inc_by := ' INCREMENT BY -';
  END IF;

  EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || seq_name || inc_by || cval;

  EXECUTE IMMEDIATE 'SELECT ' ||seq_name ||'.NEXTVAL FROM dual' INTO cval;

  EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || seq_name || ' INCREMENT BY 1';
END reset_sequence;
/
 
Sequence Related Queries
Last Number Selected From Sequence SELECT sequence_name, last_number
FROM user_sequences;
Next Number From Sequence SELECT sequence_name, (last_number + increment_by) NEXT_VALUE
FROM user_sequences;

Related Topics
Data Dictionary
Identity Columns
Table Identity Columns
What's New In 19c
What's New In 20c-21c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2019 Daniel A. Morgan All Rights Reserved
  DBSecWorx