Scalable sequences Oracle Database 18C

Sequence is commonly known oracle object that is used to generate numbers in specified orders. In Oracle 18C new extra words can be defined for sequence creation:

  • SCALE | NOSCALE(default)
  • EXTEND | NOEXTEND(default)

SCALE – forces to build extra 6 digit number as prefix to normal sequence number

scalable sequence number = 6 digit number || normal sequence number

According to oracle documentation 6 digit scalable sequence offset number  = 3 digits from instance_id and 3 digits from session_id using following pattern  [(instance_id % 100) + 100] || [session_id % 1000].

So it means it’s constant in a session and changes once you reconnect.

CREATE SEQUENCE scale_seq START WITH 1 MAXVALUE 9999999 SCALE;

SELECT sequence_name, min_value, max_value, scale_flag, extend_flag 
  FROM user_sequences;

SEQUENCE_NAME   MIN_VALUE  MAX_VALUE S E
-------------- ---------- ---------- - -
SCALE_SEQ               1    9999999 Y N

In above example as default sequence scale_seq was created with NOEXTEND. This has impact on MAXVALUE that can be generated. In this case MAXVALUE was defined with 7 digits (9999999) but first 6 are consumed by 6 digit scalable sequence offset number. So in reality you can generate here [999999]9 only numbers 1..9 for last position.

SELECT 
    sys_context('USERENV', 'INSTANCE') inst_id, 
    sys_context('USERENV', 'SID') sid 
  FROM dual;

INST_ID	 SID
-------- ----
1	 132

--you can see below how first six digits are build based on
--instance_id number and sid value

SELECT scale_seq.nextval FROM dual;

   NEXTVAL
----------
   1011321

SELECT scale_seq.nextval FROM dual;

 NEXTVAL
----------
1011322

...

--9th last possible iteration for NOEXTEND

SELECT scale_seq.nextval FROM dual;

 NEXTVAL
----------
1011329

--10th try raises error

SELECT scale_seq.nextval FROM dual;

ORA-64603: NEXTVAL cannot be instantiated for SCALE_SEQ. 
Widen the sequence by 1 digits or alter sequence with SCALE EXTEND.

Following example with this issue

CREATE SEQUENCE scale_err_seq START WITH 1 MAXVALUE 999 SCALE;

SELECT scale_err_seq.nextval FROM dual;

ORA-64603: NEXTVAL cannot be instantiated for SCALE_ERR_SEQ. 
Widen the sequence by 4 digits or alter sequence with SCALE EXTEND.

To fix this problem EXTEND word can be used so 6 digit scalable sequence offset number will not consume first 6 digits from MAXVALUE definition.

CREATE SEQUENCE scale_ext_seq START WITH 9 MAXVALUE 999 SCALE EXTEND;

SELECT sequence_name, min_value, max_value, scale_flag, extend_flag 
  FROM user_sequences
ORDER BY sequence_name;

SEQUENCE_NAME   MIN_VALUE  MAX_VALUE S E
-------------- ---------- ---------- - -
SCALE_ERR_SEQ           1        999 Y N
SCALE_EXT_SEQ           1        999 Y Y
SCALE_SEQ               1    9999999 Y N

SELECT scale_ext_seq.nextval FROM dual;

NEXTVAL
----------
101132009

SELECT scale_ext_seq.nextval FROM dual;

NEXTVAL
----------
101132010

It’s possible to change sequence to be SCALE EXTEND with

ALTER SEQUENCE scale_seq SCALE EXTEND;
ALTER SEQUENCE scale_err_seq SCALE EXTEND;

or NOSCALE

ALTER SEQUENCE scale_seq NOSCALE;
ALTER SEQUENCE scale_err_seq NOSCALE;

Have a fun 🙂
Tomasz

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.