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