"We don't live in a dictatorship or a monarchy. I swore an oath in the military and in the Senate to preserve,
protect and defend the Constitution of the United States, not to mindlessly cater to the whims of Cadet Bone Spurs and clap when he demands I clap,"
~ Sen. Tammy Duckworth
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.
SQL> conn sys@pdbdev as sysdba
SQL> SELECT object_id FROM dba_objects WHERE object_name = 'AUDSES$';
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;
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;