Introduction
Since Oracle 12 we can declare columns as an identity column. Other databases call this feature “autoincrement” column. A term that can easily lead to some misunderstandings, but is already well established. In some of those databases such a column allows to reset the current value. There is no (documented) way for that with an Oracle identity column. A recent discussion in the ODC forums lead me to think whether it is possible to set an identity column to a custom value. And yes it is.
TL;DR;
- Change the increment of the sequence that supports the identity column by modifying the column.
- Increase the value by an insert+rollback.
- Reset the sequence by modifying the column again.
or use the undocumented RESTART option (12.2 onwards)
alter table demo_identity_reset
modify id generated always as identity restart start with 60;
Showcase
demo setup
First we create a table with an identity column. We use default settings for the sequence, which for example means increment +1 and cache 20.
/* prepare demo table */
create table demo_identity_reset
(id number generated always as identity primary key
,name varchar2(500)
);
Then insert some demo values.
/* add some data */
insert into demo_identity_reset (name) values ('test1');
insert into demo_identity_reset (name) values ('test2');
commit;
select * from demo_identity_reset;
| ID | NAME |
| 1 | test1 |
| 2 | test2 |
We now have a table with an identity column where some values are used and more values are currently cached in the SGA.
Set a custom value (12.1)
Gather info
First we need to find the name of the sequence. Either by looking at all sequences, but more correct would be to look at the column definitions. The name of the sequence can be seen in the default value of the identity column.
/* check data dictionary and find the sequence name*/
select * from user_sequences;
or
/* check data dictionary and find the sequence name*/
select data_default
from user_tab_columns
where table_name = 'DEMO_IDENTITY_RESET'
and identity_column = 'YES';
DATA_DEFAULT "MYSCHEMA"."ISEQ$$_172206".nextval
In my case the sequence name is ISEQ$$_172206. The generated name will always start with ISEQ$$_.
Other information we need to find is the current value, the cache size and the increment. The only decent way to see the current value is to set the sequence to nocache and after that check the data dictionary. We need to remember the original cache size setting to be able to restore it later to that value.
Start the change
From this step onward no other session should insert at the same time. It might be a good idea to lock the table in exclusive mode during those steps.
The cache also needs to be set to nocache to prevent caching any values with the wrong increment during the next steps.
/* reset the cache so that we can see the next value */
select cache_size
from user_sequences
where sequence_name = 'ISEQ$$_172206';
alter table demo_identity_reset
modify id generated always as identity nocache;
Cache size was 20. The sequence now is not caching anymore. This is shown in the user_sequences dictionary view.
Now we can read the next value from the data dictionary and use that information to set an increment that jumps to our target value.
Here I assume a target value of 60.
/* find the next value */
select last_number from user_sequences
where sequence_name = 'ISEQ$$_172206';
/* calculate the increment: 57 = 60(target value) - 3(last number) */
/* change the increment so that it jumps over all the other values. */
alter table demo_identity_reset
modify id generated always as identity increment by 57 nocache;
The last number was 3. Last number will always hold the next value after the cached values.
-- this does a sequence.nextval which we can not call otherwise
insert into demo_identity_reset (name)
values ('dummy. rollback immediatly.');
rollback;
It doesn’t matter much, which value was inserted (59). In case you want to avoid any gaps, then this insert needs to be one of the real inserts that needs to be done. And the increment needs to be one value more (58 instead of 57). So that instead of a rollback you can do a commit.
/* reset the changes */
alter table demo_identity_reset
modify id generated always as identity increment by 1 cache 20;
Done. The next insert will now start with our target value 60.
Set a custom value (12.2)
I tested this in 19c on LiveSql but it should work in all versions that belong to the 12.2. database family (12.2.0.1, 18c, 19c).
Instead of the complicated series of steps in 12.1 we can do it all in a single command. Unfortunately this command is undocumented, so use it at your own risk.
alter table demo_identity_reset
modify id generated always as identity restart start with 60;
The RESTART option was mentioned in one of the new feature guides and Roger Troller made me aware of it in one of his blog posts. However currently (19c) it is still missing in the SQL reference documentation.
Test the change
The following steps are not needed anymore. But they are proof that the concepts works.
/* check the result in the dictionary*/
select last_number, cache_size
from user_sequences
where sequence_name = 'ISEQ$$_172206';
LAST_NUMBER CACHE_SIZE 60 20
/* test the result using the the table*/
insert into demo_identity_reset (name) values ('test3');
commit;
select * from demo_identity_reset;
| ID | NAME |
| 1 | test1 |
| 2 | test2 |
| 60 | test3 |
Yes it works!
Cleanup code
Drop the table and the sequence. A simple drop table is not enough, because the sequence will continue to exist as long as the table is still in the recycle bin.
/* undo testcase */
drop table demo_identity_reset purge;
Further information
In general I would not recommend doing that in a production environment. But there are situations where you want to do it, for example after coping a production schema to a test database and cleaning out many values, you might like to reset the sequences too.
Such situations are rare and probably once in a while activities. That is also why I didn’t include the lock table command. And the lock will only hold until the next alter table command. Which makes it not safe to use.
It is not possible to use an alter sequence command for sequences that are connected with an identity column. It results in an error message.
alter sequence "ISEQ$$_172206" restart start with 60;
ORA-32793: cannot alter a system-generated sequence
Here is an example of the definition of an autoincrement column in another database (MySQL).
https://dev.mysql.com/doc/refman/5.7/en/example-auto-increment.html
The
MySQL 5.7 Reference Manual – 3.6.9 Using AUTO_INCREMENTAUTO_INCREMENTattribute can be used to generate a unique identity for new rows
…
No value was specified for theAUTO_INCREMENTcolumn, so MySQL assigned sequence numbers automatically. You can also explicitly assign 0 to the column to generate sequence numbers, unless theNO_AUTO_VALUE_ON_ZEROSQL mode is enabled.
…
If the column is declaredNOT NULL, it is also possible to assignNULLto the column to generate sequence numbers.
…
When you insert any other value into anAUTO_INCREMENTcolumn, the column is set to that value and the sequence is reset so that the next automatically generated value follows sequentially from the largest column value.
…
Updating an existingAUTO_INCREMENTcolumn value in anInnoDBtable does not reset theAUTO_INCREMENTsequence as it does forMyISAMandNDBtables.
…
svenweller 