Oracle 18cR3 Sequence

Oracle Sequences Version 18.3.0.1

General Information

Library Note

Morgan's Library Page Header

For how many years have you been working with physical servers that are starving your database of the memory necessary to deploy important new performance features such as the Result Cache, Memoptimize Pool, In-Memory Aggregation, In-Memory Column Store, and Full Database Caching? Too long? Contact me to learn how to improve all queries ... not just some queries.

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;