How Do I Reset a Sequence in Oracle?

Resetting Oracle Sequences: A Practical Guide

Oracle sequences, crucial for generating unique numerical values, lack the straightforward RESTART WITH clause found in PostgreSQL. This guide details a stored procedure to effectively reset an Oracle sequence.

The Oracle Solution: A Stored Procedure

The following procedure provides the equivalent functionality in Oracle:

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

Procedure Usage

To reset a sequence, call the procedure, supplying the sequence name as a parameter:

<code class="language-sql">execute reset_seq('MY_SEQUENCE');</code>

This will return a ROWCOUNT of 0.

Important Considerations

• This method cleverly resets the sequence to 0 by decrementing the current value by itself.
• A subsequent increment by 1 ensures the next generated value is 1, maintaining sequence integrity. This prevents gaps in the generated numbers.

This procedure offers a reliable and efficient way to manage sequence resets within your Oracle database.

The above is the detailed content of How Do I Reset a Sequence in Oracle?. For more information, please follow other related articles on the PHP Chinese website!