Live SQL

Scalable Sequences

View All Scripts Login to Run Script

Script Name Scalable Sequences
Description Examples of how scalable sequences in 18c work
Area SQL General
Contributor Chris Saxon (Oracle)
Created Friday February 09, 2018

Statement 1
```
create sequence s 
```
```
Sequence created.
```
Statement 2
By default sequences are noscale noextend, which you can see in the scale_flag and extend_flag columns in *_sequences.
```
select scale_flag, extend_flag    
from   user_sequences 
```
```
SCALE_FLAG EXTEND_FLAG
N N
```
Statement 3
You can alter an existing sequence to have scale and extend
```
alter sequence s scale extend 
```
```
Sequence altered.
```

Statement 4

select scale_flag, extend_flag  
from   user_sequences


SCALE_FLAG EXTEND_FLAG
Y Y

Statement 5
Scale adds a six digit prefix for the sequence number. This a three digit instance number followed by a three digit session number. These are generated by: Instance number is [(instance id % 100) + 100] Session number is [session id % 1000]
```
select s.nextval from dual 
```
```
NEXTVAL
1028780000000000000000000000000001
```

Statement 6

alter sequence s maxvalue 9999999

Sequence altered.

Statement 7
When the sequence is extend, the database adds the scale digits on top of the digits for the maxvalue. So with a seven digit maxvalue, the sequence number is 13 digits long.
```
select s.nextval from dual 
```
```
NEXTVAL
1028780000002
```

Statement 8

alter sequence s scale noextend

Sequence altered.

Statement 9
When a sequence is noextend, the database includes the scale digits within the digits for maxvalue. So a 7 digit maxvalue gives sequence values 7 digits long.
```
select s.nextval     
from   dual connect by level <= 7 
```
```
NEXTVAL
1028783
1028784
1028785
1028786
1028787
1028788
1028789

7 rows selected.
```

Statement 10

This limits the sequence to generating 9 values (starting from 1)!

select s.nextval from dual

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

More Details: https://docs.oracle.com/error-help/db/ora-64603

Statement 11

alter sequence s maxvalue 99999999

Sequence altered.

Statement 12
With an 8 digit maxvalue, you can use the sequence again
```
select s.nextval from dual 
```
```
NEXTVAL
10287810
```

Statement 13

alter sequence s noscale

Sequence altered.

Statement 14
You can change a scalable sequence to a regular sequence. The sequence continues generating numbers from the upper limit for the scale values.
```
select s.nextval from dual 
```
```
NEXTVAL
19999911
```

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database