Oracle Sequences
Version 18.3.0.1

General Information
Library Note Morgan's Library Page Header
Coming to OpenWorld 2018? Be sure to visit the TidalScale booth in Moscone South and learn how to solve performance problems and lower costs with Software Defined Servers. Before you visit the booth, or if you can't make it this year, check out TidalScale at www.tidalscale.com. Be sure to click on the Solutions link and look through the Oracle resources.
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.
SQL> conn sys@pdbdev as sysdba

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

OBJECT_ID
----------
      510

SQL> DECLARE
  2   v_rid rowid;
  3   v_type number;
  4   v_obj number;
  5   v_rfno number;
  6   v_rno number;
  7   v_bno number;
  8  BEGIN
  9    SELECT rowid INTO v_rid
 10    FROM sys.seq$
 11    WHERE obj# = 510;
 12
 13    dbms_rowid.rowid_info(v_rid, v_type, v_obj, v_rfno, v_bno, v_rno);
 14    dbms_output.put_line('v_rfno= "'||v_rfno||'"');
 15    dbms_output.put_line('v_bno= "'||v_bno||'"');
 16  END;
 17  /
v_rfno= "1"
v_bno= "1121"

PL/SQL procedure successfully completed.

SQL> DECLARE
  2   v_rid ROWID;
  3   v_type NUMBER;
  4   v_obj NUMBER;
  5   v_rfno NUMBER;
  6   v_rno NUMBER;
  7   v_bno NUMBER;
  8
  9   CURSOR so_cur IS
 10   SELECT object_name, object_id, cache_size
 11   FROM dba_objects do, dba_sequences ds
 12   WHERE do.object_type = 'SEQUENCE'
 13   AND do.owner = 'SYS'
 14   AND ds.sequence_owner = 'SYS'
 15   AND do.object_name = ds.sequence_name
 16   ORDER BY 3,2 DESC;
 17  BEGIN
 18    FOR so_rec IN so_cur LOOP
 19      SELECT rowid INTO v_rid
 20      FROM sys.seq$
 21      WHERE obj# = so_rec.object_id;
 22
 23      dbms_rowid.rowid_info(v_rid, v_type, v_obj, v_rfno, v_bno, v_rno);
 24      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);
 25    END LOOP;
 26* END;
 27  /
UTL_RECOMP_SEQ                 cache size:     0 stored at: v_rfno=1 and v_bno= 1124
AWREPORT_S$                    cache size:     0 stored at: v_rfno=1 and v_bno= 1123
AWXML_S$                       cache size:     0 stored at: v_rfno=1 and v_bno= 1123
AWCREATE10G_S$                 cache size:     0 stored at: v_rfno=1 and v_bno= 1123
AWCREATE_S$                    cache size:     0 stored at: v_rfno=1 and v_bno= 1123
AWMD_S$                        cache size:     0 stored at: v_rfno=1 and v_bno= 1123
EXPRESS_S$                     cache size:     0 stored at: v_rfno=1 and v_bno= 1123
HS_BULK_SEQ                    cache size:     0 stored at: v_rfno=1 and v_bno= 1122
STREAMS$_RULE_NAME_S           cache size:     0 stored at: v_rfno=1 and v_bno= 1122
FGR$_NAMES_S                   cache size:     0 stored at: v_rfno=1 and v_bno= 1122
STREAMS$_APPLY_SPILL_TXNKEY_S  cache size:     0 stored at: v_rfno=1 and v_bno= 1122
PRIV_UNUSED_ID$                cache size:     0 stored at: v_rfno=1 and v_bno= 1122
PRIV_USED_ID$                  cache size:     0 stored at: v_rfno=1 and v_bno= 1122
PRIV_CAPTURE_SEQ$              cache size:     0 stored at: v_rfno=1 and v_bno= 1122
WRI$_SQLSET_REF_ID_SEQ         cache size:     0 stored at: v_rfno=1 and v_bno= 1122
WRI$_SQLSET_ID_SEQ             cache size:     0 stored at: v_rfno=1 and v_bno= 1122
SCHEDULER$_LWJOB_OID_SEQ       cache size:     0 stored at: v_rfno=1 and v_bno= 1121
WRP$_REPORT_ID_SEQ             cache size:     0 stored at: v_rfno=1 and v_bno= 1121
CLI_ID$                        cache size:     0 stored at: v_rfno=1 and v_bno= 1121
AWSEQ$                         cache size:     0 stored at: v_rfno=1 and v_bno= 1121
CONFLICT_HANDLER_ID_SEQ$       cache size:     0 stored at: v_rfno=1 and v_bno= 1121
COMPARISON_SEQ$                cache size:     0 stored at: v_rfno=1 and v_bno= 1121
STREAMS$_SM_ID                 cache size:     0 stored at: v_rfno=1 and v_bno= 1121
APPLY$_DEST_OBJ_ID             cache size:     0 stored at: v_rfno=1 and v_bno= 1121
APPLY$_SOURCE_OBJ_ID           cache size:     0 stored at: v_rfno=1 and v_bno= 1121
STREAMS$_PROPAGATION_SEQNUM    cache size:     0 stored at: v_rfno=1 and v_bno= 1121
STREAMS$_CAPTURE_INST          cache size:     0 stored at: v_rfno=1 and v_bno= 1121
STREAMS$_CAP_SUB_INST          cache size:     0 stored at: v_rfno=1 and v_bno= 1121
RADM_PE$_SEQ                   cache size:     0 stored at: v_rfno=1 and v_bno= 1121
PROFNUM$                       cache size:     0 stored at: v_rfno=1 and v_bno= 1121
APP$SYSTEM$SEQ                 cache size:     0 stored at: v_rfno=1 and v_bno= 1121
DBFS_HS$_BACKUPFILEIDSEQ       cache size:     2 stored at: v_rfno=1 and v_bno= 1122
DBFS_HS$_POLICYIDSEQ           cache size:     2 stored at: v_rfno=1 and v_bno= 1122
DBFS_HS$_TARBALLSEQ            cache size:     2 stored at: v_rfno=1 and v_bno= 1122
DBFS_HS$_ARCHIVEREFIDSEQ       cache size:     2 stored at: v_rfno=1 and v_bno= 1122
DBFS_HS$_STOREIDSEQ            cache size:     2 stored at: v_rfno=1 and v_bno= 1122
LINK_SOURCE_ID_SEQ             cache size:    10 stored at: v_rfno=1 and v_bno= 1122
WRI$_ADV_SEQ_SQLW_QUERY        cache size:    10 stored at: v_rfno=1 and v_bno= 1122
WI$_JOB_ID                     cache size:    10 stored at: v_rfno=1 and v_bno= 1122
WRR$_REPLAY_ID                 cache size:    10 stored at: v_rfno=1 and v_bno= 1122
WRR$_CAPTURE_ID                cache size:    10 stored at: v_rfno=1 and v_bno= 1122
WRI$_ADV_SEQ_MSGGROUP          cache size:    10 stored at: v_rfno=1 and v_bno= 1121
WRI$_ADV_SEQ_JOURNAL           cache size:    10 stored at: v_rfno=1 and v_bno= 1121
WRI$_ADV_SEQ_DIR_INST          cache size:    10 stored at: v_rfno=1 and v_bno= 1121
WRI$_ADV_SEQ_DIR               cache size:    10 stored at: v_rfno=1 and v_bno= 1121
WRI$_ADV_SEQ_EXEC              cache size:    10 stored at: v_rfno=1 and v_bno= 1121
WRI$_ADV_SEQ_TASK              cache size:    10 stored at: v_rfno=1 and v_bno= 1121
SSCR_CAP_SEQ$                  cache size:    10 stored at: v_rfno=1 and v_bno= 1121
ORA_PLAN_ID_SEQ$               cache size:    10 stored at: v_rfno=1 and v_bno= 1121
AWLOGSEQ$                      cache size:    10 stored at: v_rfno=1 and v_bno= 1121
LOG$SEQUENCE                   cache size:    10 stored at: v_rfno=1 and v_bno= 1121
TSM_MIG_SEQ$                   cache size:    10 stored at: v_rfno=1 and v_bno= 1121
PDB_ALERT_SEQUENCE             cache size:    10 stored at: v_rfno=1 and v_bno= 1121
UGROUP_SEQUENCE                cache size:    10 stored at: v_rfno=1 and v_bno= 1121
OLAP_PROPERTIES_SEQ            cache size:    20 stored at: v_rfno=1 and v_bno= 1123
OLAP_CALCULATED_MEMBERS_SEQ    cache size:    20 stored at: v_rfno=1 and v_bno= 1123
OLAP_ASSIGNMENTS_SEQ           cache size:    20 stored at: v_rfno=1 and v_bno= 1123
OLAP_MODELS_SEQ                cache size:    20 stored at: v_rfno=1 and v_bno= 1123
OLAP_MEASURES_SEQ              cache size:    20 stored at: v_rfno=1 and v_bno= 1123
OLAP_MAPPINGS_SEQ              cache size:    20 stored at: v_rfno=1 and v_bno= 1123
OLAP_HIER_LEVELS_SEQ           cache size:    20 stored at: v_rfno=1 and v_bno= 1123
OLAP_HIERARCHIES_SEQ           cache size:    20 stored at: v_rfno=1 and v_bno= 1123
OLAP_DIM_LEVELS_SEQ            cache size:    20 stored at: v_rfno=1 and v_bno= 1123
OLAP_DIMENSIONALITY_SEQ        cache size:    20 stored at: v_rfno=1 and v_bno= 1123
OLAP_ATTRIBUTES_SEQ            cache size:    20 stored at: v_rfno=1 and v_bno= 1123
JAVA$PREFS$SEQ$                cache size:    20 stored at: v_rfno=1 and v_bno= 1123
JAVA$POLICY$SEQUENCE$          cache size:    20 stored at: v_rfno=1 and v_bno= 1123
AQ$_SYS$SERVICE_METRICS_TAB_N  cache size:    20 stored at: v_rfno=1 and v_bno= 1122
AQ$_AQ_PROP_TABLE_N            cache size:    20 stored at: v_rfno=1 and v_bno= 1122
AQ$_AQ$_MEM_MC_N               cache size:    20 stored at: v_rfno=1 and v_bno= 1122
HS$_INST_INIT_S                cache size:    20 stored at: v_rfno=1 and v_bno= 1122
HS$_CLASS_INIT_S               cache size:    20 stored at: v_rfno=1 and v_bno= 1122
HS$_INST_DD_S                  cache size:    20 stored at: v_rfno=1 and v_bno= 1122
HS$_CLASS_DD_S                 cache size:    20 stored at: v_rfno=1 and v_bno= 1122
HS$_BASE_DD_S                  cache size:    20 stored at: v_rfno=1 and v_bno= 1122
HS$_INST_CAPS_S                cache size:    20 stored at: v_rfno=1 and v_bno= 1122
HS$_CLASS_CAPS_S               cache size:    20 stored at: v_rfno=1 and v_bno= 1122
HS$_FDS_INST_S                 cache size:    20 stored at: v_rfno=1 and v_bno= 1122
HS$_FDS_CLASS_S                cache size:    20 stored at: v_rfno=1 and v_bno= 1122
AQ$_KUPC$DATAPUMP_QUETAB_N     cache size:    20 stored at: v_rfno=1 and v_bno= 1122
AQ$_ORA$PREPLUGIN_BACKUP_QTB_N cache size:    20 stored at: v_rfno=1 and v_bno= 1122
ROPP$X$KCCLH_SEQ               cache size:    20 stored at: v_rfno=1 and v_bno= 1122
ROPP$X$KCCOR_SEQ               cache size:    20 stored at: v_rfno=1 and v_bno= 1122
ROPP$X$KCCTIR_SEQ              cache size:    20 stored at: v_rfno=1 and v_bno= 1122
ROPP$X$KCCSL_SEQ               cache size:    20 stored at: v_rfno=1 and v_bno= 1122
ROPP$X$KCCLE_SEQ               cache size:    20 stored at: v_rfno=1 and v_bno= 1122
ROPP$X$KCCRT_SEQ               cache size:    20 stored at: v_rfno=1 and v_bno= 1122
ROPP$X$KCVFHALL_SEQ            cache size:    20 stored at: v_rfno=1 and v_bno= 1122
ROPP$X$KCVFHTMP_SEQ            cache size:    20 stored at: v_rfno=1 and v_bno= 1122
ROPP$X$KCVFH_SEQ               cache size:    20 stored at: v_rfno=1 and v_bno= 1122
ROPP$X$KCCTF_SEQ               cache size:    20 stored at: v_rfno=1 and v_bno= 1122
ROPP$X$KCVDF_SEQ               cache size:    20 stored at: v_rfno=1 and v_bno= 1122
ROPP$X$KCCFN_SEQ               cache size:    20 stored at: v_rfno=1 and v_bno= 1122
ROPP$X$KCCFE_SEQ               cache size:    20 stored at: v_rfno=1 and v_bno= 1122
ROPP$X$KCCTS_SEQ               cache size:    20 stored at: v_rfno=1 and v_bno= 1122
ROPP$X$KCPDBINC_SEQ            cache size:    20 stored at: v_rfno=1 and v_bno= 1122
ROPP$X$KCCPDB_SEQ              cache size:    20 stored at: v_rfno=1 and v_bno= 1122
ROPP$X$KCCIC_SEQ               cache size:    20 stored at: v_rfno=1 and v_bno= 1122
ROPP$X$KCCDI2_SEQ              cache size:    20 stored at: v_rfno=1 and v_bno= 1122
ROPP$X$KCCDI_SEQ               cache size:    20 stored at: v_rfno=1 and v_bno= 1122
ROPP$X$KCCPC_SEQ               cache size:    20 stored at: v_rfno=1 and v_bno= 1122
ROPP$X$KCCDC_SEQ               cache size:    20 stored at: v_rfno=1 and v_bno= 1122
ROPP$X$KCCBI_SEQ               cache size:    20 stored at: v_rfno=1 and v_bno= 1122
ROPP$X$KCCBL_SEQ               cache size:    20 stored at: v_rfno=1 and v_bno= 1122
ROPP$X$KCCBF_SEQ               cache size:    20 stored at: v_rfno=1 and v_bno= 1122
ROPP$X$KCCBP_SEQ               cache size:    20 stored at: v_rfno=1 and v_bno= 1122
ROPP$X$KCCBS_SEQ               cache size:    20 stored at: v_rfno=1 and v_bno= 1122
ROPP$X$KCCAL_SEQ               cache size:    20 stored at: v_rfno=1 and v_bno= 1122
AQ$_ALERT_QT_N                 cache size:    20 stored at: v_rfno=1 and v_bno= 1122
AQ$_SCHEDULER_FILEWATCHER_QT_N cache size:    20 stored at: v_rfno=1 and v_bno= 1122
AQ$_SCHEDULER$_REMDB_JOBQTAB_N cache size:    20 stored at: v_rfno=1 and v_bno= 1122
AQ$_SCHEDULER$_EVENT_QTAB_N    cache size:    20 stored at: v_rfno=1 and v_bno= 1122
JOBSEQLSBY                     cache size:    20 stored at: v_rfno=1 and v_bno= 1122
PLSQL_CODE_COVERAGE_RUNNUMBER  cache size:    20 stored at: v_rfno=1 and v_bno= 1122
DBMS_LOCK_ID_V2                cache size:    20 stored at: v_rfno=1 and v_bno= 1122
PCLX_JOBSEQ                    cache size:    20 stored at: v_rfno=1 and v_bno= 1122
IDX_RB$JOBSEQ                  cache size:    20 stored at: v_rfno=1 and v_bno= 1122
MV_RF$USAGESTATSEQ             cache size:    20 stored at: v_rfno=1 and v_bno= 1122
MV_RF$JOBSEQ                   cache size:    20 stored at: v_rfno=1 and v_bno= 1122
SYSLSBY_EDS_DDL_SEQ            cache size:    20 stored at: v_rfno=1 and v_bno= 1122
SYNOPSIS_NUM_SEQ               cache size:    20 stored at: v_rfno=1 and v_bno= 1122
GROUP_NUM_SEQ                  cache size:    20 stored at: v_rfno=1 and v_bno= 1122
SQL_TK_CHK_ID                  cache size:    20 stored at: v_rfno=1 and v_bno= 1122
SYSDBIMFSSEG_SEQ$              cache size:    20 stored at: v_rfno=1 and v_bno= 1122
SYSDBIMFSCUID_SEQ$             cache size:    20 stored at: v_rfno=1 and v_bno= 1122
IM_DOMAINSEQ$                  cache size:    20 stored at: v_rfno=1 and v_bno= 1122
MVREF$_STATS_SEQ               cache size:    20 stored at: v_rfno=1 and v_bno= 1122
FED$STMT_SEQ                   cache size:    20 stored at: v_rfno=1 and v_bno= 1122
XS$ID_SEQUENCE                 cache size:    20 stored at: v_rfno=1 and v_bno= 1122
DBFS_HS$_RSEQ                  cache size:    20 stored at: v_rfno=1 and v_bno= 1122
DBMS_PARALLEL_EXECUTE_SEQ$     cache size:    20 stored at: v_rfno=1 and v_bno= 1122
WRI$_ALERT_THRSLOG_SEQUENCE    cache size:    20 stored at: v_rfno=1 and v_bno= 1121
WRI$_ALERT_SEQUENCE            cache size:    20 stored at: v_rfno=1 and v_bno= 1121
SCHEDULER$_JOBSUFFIX_S         cache size:    20 stored at: v_rfno=1 and v_bno= 1121
SCHEDULER$_EVTSEQ              cache size:    20 stored at: v_rfno=1 and v_bno= 1121
SCHEDULER$_RDB_SEQ             cache size:    20 stored at: v_rfno=1 and v_bno= 1121
SCHEDULER$_INSTANCE_S          cache size:    20 stored at: v_rfno=1 and v_bno= 1121
AQ$_NONDURSUB_SEQUENCE         cache size:    20 stored at: v_rfno=1 and v_bno= 1121
AQ$_CHAINSEQ                   cache size:    20 stored at: v_rfno=1 and v_bno= 1121
AQ$_PROPAGATION_SEQUENCE       cache size:    20 stored at: v_rfno=1 and v_bno= 1121
CHNF$_CLAUSEID_SEQ             cache size:    20 stored at: v_rfno=1 and v_bno= 1121
CHNF$_QUERYID_SEQ              cache size:    20 stored at: v_rfno=1 and v_bno= 1121
TSDP_POLNAME$SEQUENCE          cache size:    20 stored at: v_rfno=1 and v_bno= 1121
TSDP_PROTECTION$SEQUENCE       cache size:    20 stored at: v_rfno=1 and v_bno= 1121
TSDP_ASSOCIATION$SEQUENCE      cache size:    20 stored at: v_rfno=1 and v_bno= 1121
TSDP_SUBPOL$SEQUENCE           cache size:    20 stored at: v_rfno=1 and v_bno= 1121
TSDP_POLICY$SEQUENCE           cache size:    20 stored at: v_rfno=1 and v_bno= 1121
TSDP_TYPE$SEQUENCE             cache size:    20 stored at: v_rfno=1 and v_bno= 1121
TSDP_SOURCE$SEQUENCE           cache size:    20 stored at: v_rfno=1 and v_bno= 1121
TSDP_SENSITIVE$SEQUENCE        cache size:    20 stored at: v_rfno=1 and v_bno= 1121
ADO_IMCSEQ$                    cache size:    20 stored at: v_rfno=1 and v_bno= 1121
ILM_EXECUTIONID                cache size:    20 stored at: v_rfno=1 and v_bno= 1121
ILM_SEQ$                       cache size:    20 stored at: v_rfno=1 and v_bno= 1121
AQ$_TRANS_SEQUENCE             cache size:    20 stored at: v_rfno=1 and v_bno= 1121
DM$EXPIMP_ID_SEQ               cache size:    20 stored at: v_rfno=1 and v_bno= 1121
REDEF_SEQ$                     cache size:    20 stored at: v_rfno=1 and v_bno= 1121
SYNCREF_STEP_SEQ$              cache size:    20 stored at: v_rfno=1 and v_bno= 1121
SYNCREF_GROUP_ID_SEQ$          cache size:    20 stored at: v_rfno=1 and v_bno= 1121
COMPARISON_SCAN_SEQ$           cache size:    20 stored at: v_rfno=1 and v_bno= 1121
APPLY$_ERROR_HANDLER_SEQUENCE  cache size:    20 stored at: v_rfno=1 and v_bno= 1121
STREAMS$_STMT_HANDLER_SEQ      cache size:    20 stored at: v_rfno=1 and v_bno= 1121
CDC_SUBSCRIBE_SEQ$             cache size:    20 stored at: v_rfno=1 and v_bno= 1121
RGROUPSEQ                      cache size:    20 stored at: v_rfno=1 and v_bno= 1121
SNAPSITE_ID$                   cache size:    20 stored at: v_rfno=1 and v_bno= 1121
SNAPSHOT_ID$                   cache size:    20 stored at: v_rfno=1 and v_bno= 1121
PARTITION_NAME$                cache size:    20 stored at: v_rfno=1 and v_bno= 1121
STATS_ADVISOR_DIR_SEQ          cache size:    20 stored at: v_rfno=1 and v_bno= 1121
ST_OPR_ID_SEQ                  cache size:    20 stored at: v_rfno=1 and v_bno= 1121
CACHE_STATS_SEQ_0              cache size:    20 stored at: v_rfno=1 and v_bno= 1121
CACHE_STATS_SEQ_1              cache size:    20 stored at: v_rfno=1 and v_bno= 1121
DAM_CLEANUP_SEQ$               cache size:    20 stored at: v_rfno=1 and v_bno= 1121
XSPARAM_REG_SEQUENCE$          cache size:    20 stored at: v_rfno=1 and v_bno= 1121
USER_GRANT                     cache size:    20 stored at: v_rfno=1 and v_bno= 1121
SYSTEM_GRANT                   cache size:    20 stored at: v_rfno=1 and v_bno= 1121
OBJECT_GRANT                   cache size:    20 stored at: v_rfno=1 and v_bno= 1121
JOBSEQ                         cache size:    20 stored at: v_rfno=1 and v_bno= 1121
SQLLOG$_SEQ                    cache size:    20 stored at: v_rfno=1 and v_bno= 1121
FED$SESS_SEQ                   cache size:    20 stored at: v_rfno=1 and v_bno= 1121
FED$APPID_SEQ                  cache size:    20 stored at: v_rfno=1 and v_bno= 1121
DBMS_CUBE_ADVICE_SEQ$          cache size:   100 stored at: v_rfno=1 and v_bno= 1123
WRI$_SQLSET_RATMASK_SEQ        cache size:   100 stored at: v_rfno=1 and v_bno= 1122
WRI$_SQLSET_WORKSPACE_PLAN_SEQ cache size:   100 stored at: v_rfno=1 and v_bno= 1122
WRI$_SQLSET_STMT_ID_SEQ        cache size:   100 stored at: v_rfno=1 and v_bno= 1122
WRI$_ADV_SQLT_PLAN_SEQ         cache size:   100 stored at: v_rfno=1 and v_bno= 1121
WRI$_REPT_FORMAT_ID_SEQ        cache size:   100 stored at: v_rfno=1 and v_bno= 1121
WRI$_REPT_REPT_ID_SEQ          cache size:   100 stored at: v_rfno=1 and v_bno= 1121
WRI$_REPT_COMP_ID_SEQ          cache size:   100 stored at: v_rfno=1 and v_bno= 1121
WRI$_EMX_FILE_ID_SEQ           cache size:   100 stored at: v_rfno=1 and v_bno= 1121
INVALIDATION_REG_ID$           cache size:   300 stored at: v_rfno=1 and v_bno= 1121
AQ$_PUBLISHER_SEQUENCE         cache size:  1000 stored at: v_rfno=1 and v_bno= 1121
AQ$_RULE_SEQUENCE              cache size:  1000 stored at: v_rfno=1 and v_bno= 1121
AQ$_RULE_SET_SEQUENCE          cache size:  1000 stored at: v_rfno=1 and v_bno= 1121
AQ$_IOTENQTXID                 cache size:  1000 stored at: v_rfno=1 and v_bno= 1121
IDGEN1$                        cache size:  1000 stored at: v_rfno=1 and v_bno= 1121
PSINDEX_SEQ$                   cache size:  1000 stored at: v_rfno=1 and v_bno= 1121
DBFS_SFS$_FSSEQ                cache size:  8192 stored at: v_rfno=1 and v_bno= 1122
CDC_RSID_SEQ$                  cache size: 10000 stored at: v_rfno=1 and v_bno= 1121
AUDSES$                        cache size: 10000 stored at: v_rfno=1 and v_bno= 1121
ORA_TQ_BASE$                   cache size: 10000 stored at: v_rfno=1 and v_bno= 1121

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;
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 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 12cR2
What's New In 18cR1

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