Oracle Sequences

Oracle Sequences Version 11.2.0.3

General Information

Data Dictionary Objects

ALL_SEQUENCES	SEQ$	USER_SEQUENCES
DBA_SEQUENCES

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

DROP SEQUENCE seq_cache;

11.2.0.1 Deferred Segment Creation Related Bug

In 11.2.0.1 there are cases where a sequence never produces the first integer with the installation default Deferred Segment Creation.

If this is an issue change the init parameter to FALSE.

This "bug" is described fully in metalink note 1050193.1.

CREATE TABLE t (rid NUMBER, testcol VARCHAR2(25));
CREATE SEQUENCE seq;

INSERT INTO t (rid, testcol) VALUES (seq.NEXTVAL, 'Dan Morgan');

SELECT * FROM t;

INSERT INTO t (rid) testcol) VALUES (seq.NEXTVAL, 'Tom Kyte');
/
/
/

SELECT * FROM t;

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;