Oracle: Resetting an identity column vs resetting a sequence

Question

An Oracle Identity column can be reset by simply changing its start value with an alter table... modify statement. Given that identity column internally uses a sequence, why is it not possible to do the same with a sequence i.e. change its start value to a specific number with a single alter statement? So far the only way I've seen to do this is through a procedure involving multiple steps.

Or, to put it in another way, how does Oracle internally handle the altering of start value of an identity column? Does it drop and recreate the backing implicit sequence? Or is there a way to reset a sequence which is just not accessible to users?

Answer 1

Given that identity column internally uses a sequence, why is it not possible to do the same with a sequence i.e. change its start value to a specific number with a single alter statement?

Oracle Official way of resetting a sequence is dropping and recreating the sequence, of course, you need to take care about the privileges granted.

You can also use this statement to restart a sequence by dropping and then re-creating it. For example, if you have a sequence with a current value of 150 and you would like to restart the sequence with a value of 27, then you can drop the sequence and then re-create it with the same name and a START WITH value of 27.

Oracle 12c Docs

However, there is an undocumented way to reset the sequence which works for Oracle 12c.

SQL> select serial.nextval from dual;
 NEXTVAL
----------
 4
SQL> alter sequence serial restart start with 1;
Sequence altered.
SQL> select serial.nextval from dual;
 NEXTVAL
----------
 1

• EdStevens: But since it is undocumented, one would be well advised to not rely on it. I've been in this business over 35 years, first as a cobol programmer, then as a DBA on various platforms, and believe me the landscape is littered with people who came to rely on some undocumented 'feature' or behavior only to come to grief when said behavior changed. Since the behavior is undocumented the vendor is under no obligation to guarantee it and some internal change could break it at any patch.
• Balazs Papp: It is a documented "new" feature in 18c: docs.oracle.com/en/database/oracle/oracle-database/18/admin/… docs.oracle.com/en/database/oracle/oracle-database/18/admin/… The SQL Language Reference still does not have this clause...
• Chris Saxon: The restart clause is now documented in the 19c docs docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/…