How to reset Identiity Column Starting Value?

[admin]

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:

1. You inserted test data and now want to move the table to production.
2. You deleted or truncated the data and want the sequence to start from 1 again.
3. You want the identity sequence to continue from the current maximum ID.

How to Reset an Identity Column in Oracle

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);

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:

Code: Select all

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

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:

Code: Select all

TRUNCATE TABLE JS_INVOICE_HEADER;
ALTER TABLE JS_INVOICE_HEADER MODIFY ID GENERATED AS IDENTITY (START WITH 1);

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);