CODEWITHSUNDEEP
oracle-databaseidentity
sims
sims

Reputation: 91

How to reset IDENTITY column in oracle to a new value

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. ?

Upvotes: 8

Views: 16361

Answers (2)

Scott
Scott

Reputation: 5035

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

Upvotes: 10

knagaev
knagaev

Reputation: 2957

No direct keyword and you have to alter table (right words are START WITH).
There's another way to change a highwatermark for identity column. As for common sequences (by the way identity is built on a system sequence) use an old trick with INCREMENT BY for shifting mark in any directions.

ALTER TABLE T
MODIFY (ID NUMBER GENERATED ALWAYS AS IDENTITY
INCREMENT BY 50
NOCACHE);

That is, if for now next value is 100 and you need to make it 1000, you do this trick two times: "increment by 900", add record and back "increment by 1" (if identity with step 1).

Upvotes: 0

Related Questions