1

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?

Improve this question

1 Answer 1

Reset to default
3

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
Improve this answer
3

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.