Oracle Sequences
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Header
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.2 to 12.2.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Have you viewed the Library's main Table of Contents? If not ... [Click Here] ... scroll down the page and you will find links to the largest collection of Oracle Database demos on the net.
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

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 / as sysdba

shutdown abort;

startup

conn uwclass/uwclass

SELECT sequence_name, last_number
FROM user_sequences;

SELECT seq_cache.NEXTVAL FROM dual;
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;
Traditional PL/SQL 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 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 12cR1
What's New In 12cR2

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-2017 Daniel A. Morgan All Rights Reserved