How to Efficiently Reset an Oracle Sequence to Zero?

Effective way to reset Oracle sequence to zero

PostgreSQL uses the ALTER SEQUENCE command in conjunction with the RESTART option to efficiently reset the sequence. However, there is no direct corresponding command in Oracle.

Oracle solution: Custom process

Oracle expert Tom Kyte provides a powerful procedure for resetting a sequence to 0. This process consists of the following steps:

1. Determine the current sequence value:

   • Execute an immediate command to extract the next value of the sequence into a variable (e.g., l_val).

2. Adjust sequence increment:

   • Execute another immediate command that modifies the sequence increment by subtracting the current value, effectively lowering it to 0.
   • Set the minimum value of the sequence to 0.

3. Revert changes:

   • After resetting the current sequence value to 0, restores the delta to its original value and the minimum value to 0.

Example process:

The following customization process reset_seq summarizes the above steps:

<code class="language-sql">CREATE OR REPLACE
PROCEDURE reset_seq(p_seq_name IN VARCHAR2)
IS
    l_val NUMBER;
BEGIN
    EXECUTE IMMEDIATE 'SELECT ' || p_seq_name || '.nextval FROM DUAL' INTO l_val;

    EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || p_seq_name || ' INCREMENT BY -' || l_val || ' MINVALUE 0';

    EXECUTE IMMEDIATE 'SELECT ' || p_seq_name || '.nextval FROM DUAL' INTO l_val;

    EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || p_seq_name || ' INCREMENT BY 1 MINVALUE 0';
END;
/</code>

More resources:

• Reset sequence values ​​using dynamic SQL
• How to reset the sequence?

The above is the detailed content of How to Efficiently Reset an Oracle Sequence to Zero?. For more information, please follow other related articles on the PHP Chinese website!