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
Asked
Active
Viewed 2.4k times
10
-
You need to reset the sequence: http://stackoverflow.com/q/51470/330315 – Sep 02 '16 at 14:58
-
4If it's an identity column on 12c you could try: `ALTER TABLE SCOTT.IDENTITY_TEST_TABLE MODIFY(ID Generated as Identity (START WITH 1));` – vercelli Sep 02 '16 at 15:29
-
Thank you!!! It works correctly – Tigran Simonyan Sep 05 '16 at 13:57
1 Answers
17
From Oracle 12c and above version, you can do it in 3 ways:
- Manually reset next value of the sequence to specific value:
ALTER TABLE [TableName] MODIFY(ID GENERATED AS IDENTITY (START WITH 1));
- 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');
- 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
mijaved
- 671
- 9
- 11