ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling
complimentary technical Workshops on Database Security for the first 30
Oracle Database customers located anywhere in North America, EMEA, LATAM, or
APAC that send an email to
asra_us@oracle.com. Request a Workshop for
your organization today.
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
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;
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;
/* 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;
CREATE SEQUENCE seq_shard
SHARD EXTEND;
*
ERROR at line 1:
ORA-02511: SQL query not allowed; the shard DDL is disabled.
CREATE SEQUENCE seq_noshard NOSHARD;
CREATE SEQUENCE seq_noshard NOSHARD
*
ERROR at line 1:
ORA-02511: SQL query not allowed; the shard DDL is disabled.
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;
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;
