Oracle Sequences

Search the Reference Library pages:

Oracle Sequences

Version 11.1

General

Dependent Objects

seq$
dba_sequences	all_sequences	user_sequences

Related System Privileges

create sequence
create any sequence
alter any sequence
drop any sequence
select any sequence

NOTE: 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 transation

Tables For Sequence Demos

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 297;

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;

CREATE SEQUENCE seq_cycle START WITH 1 MAXVALUE 5 CYCLE;
-- default cache is 20

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

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

ALTER SEQUENCE seq_inc_by_ten INCREMENT BY 20;

Change Max Value

ALTER SEQUENCE <sequence_name> MAX VALUE <integer>

ALTER SEQUENCE seq_maxval MAXVALUE 10;

Change Cycle

ALTER SEQUENCE <sequence_name> <CYCLE | NOCYCLE>

ALTER SEQUENCE seq_cycle NOCYCLE;

Change Cache

ALTER SEQUENCE <sequence_name> CACHE <integer> | NOCACHE

ALTER SEQUENCE seq_cache NOCACHE;

Change Order

ALTER SEQUENCE <sequence_name> <ORDER | NOORDER>

ALTER SEQUENCE seq_order NOORDER;

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;

-----