How to reset IDENTITY column in oracle to a new value

Question

I am using IDENTITY feature of oracle 12 c to increment record id of a table.
As part of initial setup we have to migrate some records from another system to this table. these records are non sequential records(Partial records are deleted).

How to make identity to always create highest value + 1 based on the table records.

After googling for different options found keyword of RESTART WITH value option. For this we need to create a new store procedure and alter all the tables with highest possible values to restart with.

Is there any direct keyword which can be used along with IDENTITY which can force it to always regenerate higher values. ?

Solution

This has become super easy in 12c

alter table your_table modify (id generated by default on null as identity 
                               start with limit value);

Then the next insert will safely insert using a sequence that has been automatically reset to what is essentially max(id)+1 https://docs.oracle.com/database/121/SQLRF/statements_3001.htm#SQLRF01001

Comments

[sims]

RESTART WITH function is not working even-though this is recommended as option in oracle documents. Only option seems to alter table and force the IDENTITY to start from the highest value+1 .

[Scott]

"restart with" is not oracle, maybe mysql?

[William Robertson]

I always get ORA-30673: column to be modified is not an identity column when trying to make a column an identity column. It seems you can only tweak the settings of an existing identity column, not convert an existing non-identity column into one.

[Scott]

Correct. This statement is used to reset the sequence value, not to migrate the behaviour of the column. See this for an example grassroots-oracle.com/2016/10/…

[Tiemo Vorschütz]

Thanks, Scott! You made my day!