how to reset Identity column in Oracle

Question

I have a column with identity, which count is 19546542, and i want reset it after deleting all data .I need anything like 'dbcc checkident' in ms sql but in Oracle

Answer 1

From Oracle 12c and above version, you can do it in 3 ways:

1. Manually reset next value of the sequence to specific value:

ALTER TABLE [TableName] MODIFY(ID GENERATED AS IDENTITY (START WITH 1));

2. Automatically reset the next value of the sequence to the maximum ID value:

ALTER TABLE [TableName] MODIFY ID GENERATED BY DEFAULT AS IDENTITY (START WITH LIMIT VALUE);

Both the above case it will allow you to insert data with values in the identity column

insert into [TableName] (ID, Name) VALUES (1, 'Name1');
insert into [TableName] (ID, Name) VALUES (2, 'Name2');

3. Automatically reset the next value of the sequence to the maximum ID:

ALTER TABLE [TableName] MODIFY ID GENERATED ALWAYS AS IDENTITY (START WITH LIMIT VALUE);

However, in this case, it will restrict you insert with identity column values

insert into [TableName] (Name) VALUES ('Name1');
insert into [TableName] (Name) VALUES ('Name2');

You will following error if you pass values to the identity column

ORA-32795: cannot insert into a generated always identity column