How to reset Identiity Column Starting Value?
Posted: Thu Jul 31, 2025 5:13 am
This topic will help you to reset or continue Oracle identity sequences without dropping the table or manually altering internal sequences.
What is an Identity Column?
An identity column in Oracle is used to automatically generate sequential values, typically for a primary key. When you define a column as "GENERATED AS IDENTITY", Oracle creates and manages an internal sequence behind the scenes. This feature removes the need to manually create and manage a separate sequence and trigger.
Why Reset the Identity Sequence?
You may need to reset the identity column sequence in situations such as:
The method depends on the Oracle version you are using.
In Oracle 12c (12.1 and 12.2):
You can reset the identity sequence using the START WITH clause.
Example:
This only works if the table is empty, or the new value is greater than the current maximum ID. If not, Oracle will raise an error.
In Oracle 18c and Higher (including 21c and 23c):
Use START WITH LIMIT VALUE to automatically resume from the maximum existing ID plus the increment.
Example:
This is the recommended method for newer versions and is safe even when data already exists in the table.
After Truncating the Table:
If the table has been truncated (i.e., all rows deleted), use:
This is valid in Oracle 12c and higher.
Verifying Identity Column Details:
To check the identity column and its system-managed sequence:
SELECT COLUMN_NAME, GENERATION_TYPE, SEQUENCE_NAME FROM USER_TAB_IDENTITY_COLS WHERE TABLE_NAME = 'JS_INVOICE_HEADER';
Summary:
Reset to 1 after truncation (12c+):
ALTER TABLE JS_INVOICE_HEADER MODIFY ID GENERATED AS IDENTITY (START WITH 1);
Automatically resume from max value (18c+):
ALTER TABLE JS_INVOICE_HEADER MODIFY ID GENERATED BY DEFAULT AS IDENTITY (START WITH LIMIT VALUE);
What is an Identity Column?
An identity column in Oracle is used to automatically generate sequential values, typically for a primary key. When you define a column as "GENERATED AS IDENTITY", Oracle creates and manages an internal sequence behind the scenes. This feature removes the need to manually create and manage a separate sequence and trigger.
Why Reset the Identity Sequence?
You may need to reset the identity column sequence in situations such as:
- You inserted test data and now want to move the table to production.
- You deleted or truncated the data and want the sequence to start from 1 again.
- You want the identity sequence to continue from the current maximum ID.
The method depends on the Oracle version you are using.
In Oracle 12c (12.1 and 12.2):
You can reset the identity sequence using the START WITH clause.
Example:
Code: Select all
ALTER TABLE JS_INVOICE_HEADER MODIFY ID GENERATED AS IDENTITY (START WITH 1);In Oracle 18c and Higher (including 21c and 23c):
Use START WITH LIMIT VALUE to automatically resume from the maximum existing ID plus the increment.
Example:
Code: Select all
ALTER TABLE JS_INVOICE_HEADER MODIFY ID GENERATED BY DEFAULT AS IDENTITY (START WITH LIMIT VALUE);After Truncating the Table:
If the table has been truncated (i.e., all rows deleted), use:
Code: Select all
TRUNCATE TABLE JS_INVOICE_HEADER;
ALTER TABLE JS_INVOICE_HEADER MODIFY ID GENERATED AS IDENTITY (START WITH 1);Verifying Identity Column Details:
To check the identity column and its system-managed sequence:
SELECT COLUMN_NAME, GENERATION_TYPE, SEQUENCE_NAME FROM USER_TAB_IDENTITY_COLS WHERE TABLE_NAME = 'JS_INVOICE_HEADER';
Summary:
Reset to 1 after truncation (12c+):
ALTER TABLE JS_INVOICE_HEADER MODIFY ID GENERATED AS IDENTITY (START WITH 1);
Automatically resume from max value (18c+):
ALTER TABLE JS_INVOICE_HEADER MODIFY ID GENERATED BY DEFAULT AS IDENTITY (START WITH LIMIT VALUE);